IF statement with range

J

JN

I know it's something obvious but I'm getting error messages, no results...

A B
Bob 1
Sue 7
Joe 2


I need to look at the entire range of col B and if it is = to 1, give me the
corresponding text in Col A. I was using =IF(B1:B3="1",A1:A3,"") but it's
obviously wrong. My whole goal is to look at col B, find the top 5 and give
the corresponding name sin col A. Any help would be greatly appreciated!
 
M

macropod

Hi JN,

Try:
=INDEX(A1:A3,MATCH(1,B1:B3,0))
If the ranking/value for the person you want to return is in another cell, replace the '1' in the formula with the cell reference.

Cheers
 
J

JN

That is EXACTLY what I needed and I would have never come up with it on my
own! You Rule! Thank you so very, very much!

macropod said:
Hi JN,

Try:
=INDEX(A1:A3,MATCH(1,B1:B3,0))
If the ranking/value for the person you want to return is in another cell, replace the '1' in the formula with the cell reference.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

JN said:
I know it's something obvious but I'm getting error messages, no results...

A B
Bob 1
Sue 7
Joe 2


I need to look at the entire range of col B and if it is = to 1, give me the
corresponding text in Col A. I was using =IF(B1:B3="1",A1:A3,"") but it's
obviously wrong. My whole goal is to look at col B, find the top 5 and give
the corresponding name sin col A. Any help would be greatly appreciated!
Click to expand...
Click to expand...
 
J

JN

I don't suppose there is anything that can be done when I have a duplicate
ranking (2 cells with same ranking)...

macropod said:
Hi JN,

Try:
=INDEX(A1:A3,MATCH(1,B1:B3,0))
If the ranking/value for the person you want to return is in another cell, replace the '1' in the formula with the cell reference.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

JN said:
I know it's something obvious but I'm getting error messages, no results...

A B
Bob 1
Sue 7
Joe 2


I need to look at the entire range of col B and if it is = to 1, give me the
corresponding text in Col A. I was using =IF(B1:B3="1",A1:A3,"") but it's
obviously wrong. My whole goal is to look at col B, find the top 5 and give
the corresponding name sin col A. Any help would be greatly appreciated!
Click to expand...
Click to expand...
 
Top