IF Statement with a range
 

I've got the following formula to pull in labels for a table:
=IF(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE)<=3,Acco unts!L4,"")&IF(VLOOKUP($D$9,Accounts!A1:G500,7,FAL SE)=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 between
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 get
it to work.

Any help would be appreciated.
Phillycheese5


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=390638

It seems like you have already filtered out everything less than or
equal to 5. Wouldn't the next IF() statement become
IF(VLOOKUP()<=9,TRUE,FALSE)?


Phillycheese5 Wrote:
I've got the following formula to pull in labels for a table:
=IF(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE)<=3,Acco unts!L4,"")&IF(VLOOKUP($D$9,Accounts!A1:G500,7,FAL SE)=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 between
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 get
it to work.

Any help would be appreciated.
Phillycheese5


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390638

Yes, that does work (thanks)...However, I have cases where I want to put
non-consecutive results together. The VLOOKUP is referencing a table
that is in order, but this is not always the case and it's too
complicated to rearrange the table which itself is made up of
formulas.
Is there a way to use the IF function to do something if a specific
range (i.e. 5-9) or set of numbers (1,7,12,23) is the result?


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=390638

I am not quite getting your question. Can you explain more?

By the way, VLOOKUP table must be sorted in order for VLOOKUP() to
function properly.


Phillycheese5 Wrote:
Yes, that does work (thanks)...However, I have cases where I want to put
non-consecutive results together. The VLOOKUP is referencing a table
that is in order, but this is not always the case and it's too
complicated to rearrange the table which itself is made up of
formulas.
Is there a way to use the IF function to do something if a specific
range (i.e. 5-9) or set of numbers (1,7,12,23) is the result?


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390638

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

The strategy numbers are not grouped well. I want non-consecutive
strategy numbers to have the same labels without having to re-write all
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...


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=390638

You can try using 2 VLOOKUP().


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

The strategy numbers are not grouped well. I want non-consecutive
strategy numbers to have the same labels without having to re-write all
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...


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390638

I was hoping to get it into 1 cell where it checks for multiple ranges
and series of numbers. Thanks.


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=390638

It will still be within one cell but you have a VLOOKUP() within another
VLOOKUP().

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

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


Hope it helps.



Phillycheese5 Wrote:
I was hoping to get it into 1 cell where it checks for multiple ranges
and series of numbers. Thanks.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390638

One way would be to incorporate Choose
=CHOOSE(VLOOKUP($D$9,Accounts!A1:G500,7,FALSE),Acc ounts!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"
wrote in message
news:Phillycheese5.1sue3o_1122491338.1206@excelfor um-nospam.com...

I was hoping to get it into 1 cell where it checks for multiple ranges
and series of numbers. Thanks.


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile:
http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=390638