Excel help please - is there a formula for this?

[Participant]

Hi all

Imagine a row of words followed by a row of numbers.

I need something that looks along the row and says, if the number is not zero, return the word on the row above.

E.g.

STO ALC FIN MRB OUT
_0___1___0__0___0


Would return "ALC".

Many thanks .

 

[Participant]

=IF(D11=1,"ALC",IF(C11=1,"STO",IF(E11=1,"FIN",IF(F11=1,"MRB",IF(G11=1,"OUT")))))
There you go. Just change the cell references as required. If all of them are zero it just returns "false". And if more than one is 1, it will just return the first word above the first cell that is found to be 1.

Thanks very much, but there are two problems with this:

- the number isn't necessarily 1
- there are about a hundred fields of three letter words (they are stock depots) and I don't want to type that out for one hundred plus depots

I should have been more specific in the first instance.

 

[Participant]

Could there be more than one instance of a number appearing ie you might want to return more than one word?

On a few occasions yes, but for 99% of items only one word is required. For the rest I can use sort and filters to create the multiple words.

I'm wondering if pivot table count function can help me but I need to faff a bit more to see.

 

[Participant]

Thanks all, I'll try these options tomorrow .