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 .

 

[lord filbuster]

=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.

 

[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.

 

[lord filbuster]

Sorry, i can't think of a way around typing out every single one, and chaining if statments doesnt work, becuase if excel stops checking after it finds the first value higher than 0. This is the best i can come up with i'm afraid =IF(C13>0,C12,IF(D13>0,D12)) but it will end up with a stupid number of brackets.

 

[Jed Shields]

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

 

[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.

 

[londonbairn]

INDEX and MATCH

eg assume A is in A1 and 1 is in D2.

A B C D
0 0 0 1

=INDEX(B1: E1,1,MATCH(1,B2: E2,0))

will return "D". the 1 in MATCH is the lookup value

 

[Namsnik]

Sort Left to Right

You could sort the data so all the non zero items are in the first few columns.
Data,Sort,Options,Left_to_Right.........

 

[Participant]

Thanks all, I'll try these options tomorrow .

 

[Dolph]

Index and match is definitely the solution here. Doing it with if statements is fine if it's only a couple, but otherwise it'll get very unwieldy, and god help you if you have to expand it later.