Returning a number from multiple matches possible

**Jeffatoffice** · 08-03-2011, 04:00 PM

I am trying to write a formula that looks to see if cell A4=1 and if it is to return the value of cell B4. That part is easy, my issue is that if cell A4 is not value 1 then I want it to look at cell A5 and see if it is 1 and return the value in cell B5 if it is, if not go to A6 and see if it equals 1, if yes then return value of B6 else look at A7 etc...

Any ideas?

**Colin Legg** · 08-03-2011, 04:10 PM

Welcome to the forum, Jeff.

Try this formula:

=INDEX(B4:B10,MATCH(1,A4:A10,0))

If it doesn't find a 1 in any of the cells then it will return a #N/A error.

Another way to do this is with VLOOKUP:

=VLOOKUP(1,A4:B10,2,0)

**Jeffatoffice** · 08-03-2011, 04:33 PM

Where is the B10 coming from in the vlookup formula?

**Colin Legg** · 08-03-2011, 04:53 PM

Your values are in A4 through to Bx, where x is the last row. You didn't tell me where the last row of data was, so I guessed at 10 for my example. If the 1 might be found up to A100, then change to B100.