IF statement with range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with a Sum and an Array 15
IF AND statement with cell range 4
Help with IF Functions 4
Using SUMIF inside SUMPRODUCT => complex problem (suite) 1
Selcting data from table without repeating plus calc's 2
Countif with multiple criteria (incl. calculation) 3
Need Help with Macro 8
If( AND( with VLOOKUP.... 4

Back
Top