Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
If statement where the logical test is a range that equals a word | Excel Worksheet Functions | |||
Return range of values on an "IF" statement | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions |