IF Statement with a range

  • Thread starter Thread starter Phillycheese5
  • Start date Start date
P

Phillycheese5

I've got the following formula to pull in labels for a table:
=IF(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE)<=3,Accounts!L4,"")&IF(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE)=4,Accounts!M4,"")&IF(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE)=5,Accounts!N4,"")

I don't know how to ask for a range if the VLOOKUP result is betwee
5-9 instead of adding a separate line for each value of 5,6,7,8 and 9.
I tried using {5,6,7,8,9} and gave the AND/OR a shot, but couldn't ge
it to work.

Any help would be appreciated.
Phillycheese
 
I have a table which includes account name and strategy (i.e. Large Cap
Tech, Mid Growth, etc.) and a strategy number. Each strategy wil
ultimately direct which statistic labels I use when showing the summar
sheet. The data that gets pulled onto the summary then does a VLOOKU
on the label.

The strategy numbers are not grouped well. I want non-consecutiv
strategy numbers to have the same labels without having to re-write al
the strategy numbers each time I receive an updated download.

So I want to say that IF my VLOOKUP result is equal to 1,2,3 and 21
use the label "Mid Cap" for each of them.

I hope that is more clear..
 
You can try using 2 VLOOKUP().

I have a table which includes account name and strategy (i.e. Large Cap
Tech, Mid Growth, etc.) and a strategy number. Each strategy wil
ultimately direct which statistic labels I use when showing the summar
sheet. The data that gets pulled onto the summary then does a VLOOKU
on the label.

The strategy numbers are not grouped well. I want non-consecutiv
strategy numbers to have the same labels without having to re-write al
the strategy numbers each time I receive an updated download.

So I want to say that IF my VLOOKUP result is equal to 1,2,3 and 21
use the label "Mid Cap" for each of them.

I hope that is more clear..
Click to expand...
 
I was hoping to get it into 1 cell where it checks for multiple range
and series of numbers. Thanks
 
It will still be within one cell but you have a VLOOKUP() within anothe
VLOOKUP().

Assuming you are searching for account name then return a number. The
the next VLOOKUP() search for this number from another table and retur
another value(a label in your case "Mid Cap" etc)

ie.
VLOOKUP(VLOOKUP(account name, table1, return number), table2, retur
label)


Hope it helps.
 
One way would be to incorporate Choose
=CHOOSE(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE),Accounts!L4,ACCOUNTS!M4,Accounts!N4,etc...)

Obviously you would need to repeat L4 3 times to cover results 1-3 and so on
but this will give you a start.
Choose will permit up to 29 items in its list.

Incidentally, Morrigans assertion that the table must be sorted for VLOOKUP
to work is incorrect.
Provided you use the 4th argument to Vlookup, FALSE or 0, as you have, then
it will work even when the table is not sorted.

--
Regards
Roger Govier
"Phillycheese5" <[email protected]>
wrote in message
news:P[email protected]...
 
Back
Top