Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
Is is possible to look up a value (Sales) in a table of ranges and return
another value depending on which range it falls into? Code Sales Min Sales Max A 10000 B 8000 10000 C 6000 8000 D 0 6000 So say I enter 9500 in a cell, I want it to return "B" as 9500 falls witin that range of 8000-12000. I've already created an IF statement that works but as I get more and more ranges, it's going to get confusing and I think it maxes out at 7 conditions. Just thought there might be an alternative to creat a table for the ranges and do some sort of lookup or something. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
Yes, as long as the data is sorted (as yours is, but it would be
easier if it was in the opposite order to what you show), then you could use VLOOKUP. Tell us more about what you want to do. Pete On Dec 11, 4:22*pm, empire11 wrote: Is is possible to look up a value (Sales) in a table of ranges and return another value depending on which range it falls into? Code *Sales Min Sales Max A * * * *10000 B * * * * 8000 * * *10000 C * * * *6000 * * * 8000 D * * * *0 * * * * * * 6000 So say I enter 9500 in a cell, I want it to return "B" as 9500 falls witin that range of 8000-12000. I've already created an IF statement that works but as I get more and more ranges, it's going to get confusing and I think it maxes out at 7 conditions. Just thought there might be an alternative to creat a table for the ranges and do some sort of lookup or something. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
I use basic vlookups all the time so I was definitely thinking of a variation
of that. I thought a vlookup had to look up a specific value and find that exact value or the next closest value in a list of data. Maybe there's a different way to do it that I just don't know about? So with my original example, if I did a regular vlookup (as I know it) on $9500, it wouldn't work because $9500 doesn't exist in my lookup table. It's just a point between the min and max of a particular range ($8000-$10000). ....but that's not what I'm looking for. Plus I'm not looking up nice even numbers, it'll be more like $9543.87 What I want to do is see which range of data a particlar value fits into. Basically I'm trying to assign a Sales Volume to a particular location. A volume is $12000, B volume is between $10000 and $12000, etc. Hopefully that better describes what I'm after... "Pete_UK" wrote: Yes, as long as the data is sorted (as yours is, but it would be easier if it was in the opposite order to what you show), then you could use VLOOKUP. Tell us more about what you want to do. Pete On Dec 11, 4:22 pm, empire11 wrote: Is is possible to look up a value (Sales) in a table of ranges and return another value depending on which range it falls into? Code Sales Min Sales Max A 10000 B 8000 10000 C 6000 8000 D 0 6000 So say I enter 9500 in a cell, I want it to return "B" as 9500 falls witin that range of 8000-12000. I've already created an IF statement that works but as I get more and more ranges, it's going to get confusing and I think it maxes out at 7 conditions. Just thought there might be an alternative to creat a table for the ranges and do some sort of lookup or something. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
Lay out your table like this:
0 E 6000 D 8000 C 10000 B 12000 A Suppose this table is in M1:N5, and you enter a sales amount in A1. Put this formula in B1: =VLOOKUP(A1,M1:N5,2) and it will return the appropriate letter. Hope this helps. Pete On Dec 11, 6:57*pm, empire11 wrote: I use basic vlookups all the time so I was definitely thinking of a variation of that. *I thought a vlookup had to look up a specific value and find that exact value or the next closest value in a list of data. *Maybe there's a different way to do it that I just don't know about? So with my original example, if I did a regular vlookup (as I know it) on $9500, it wouldn't work because $9500 doesn't exist in my lookup table. *It's just a point between the min and max of a particular range ($8000-$10000).. * ...but that's not what I'm looking for. *Plus I'm not looking up nice even numbers, it'll be more like $9543.87 What I want to do is see which range of data a particlar value fits into. Basically I'm trying to assign a Sales Volume to a particular location. *A volume is $12000, B volume is between $10000 and $12000, etc. Hopefully that better describes what I'm after... "Pete_UK" wrote: Yes, as long as the data is sorted (as yours is, but it would be easier if it was in the opposite order to what you show), then you could use VLOOKUP. Tell us more about what you want to do. Pete On Dec 11, 4:22 pm, empire11 wrote: Is is possible to look up a value (Sales) in a table of ranges and return another value depending on which range it falls into? Code *Sales Min Sales Max A * * * *10000 B * * * * 8000 * * *10000 C * * * *6000 * * * 8000 D * * * *0 * * * * * * 6000 So say I enter 9500 in a cell, I want it to return "B" as 9500 falls witin that range of 8000-12000. I've already created an IF statement that works but as I get more and more ranges, it's going to get confusing and I think it maxes out at 7 conditions. Just thought there might be an alternative to creat a table for the ranges and do some sort of lookup or something. Thanks!- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
Yep...that works. I've only ever used lookups where I'm looking for the
exact number so alway put =VLOOKUP(A1,M1:N5,2,FALSE) so that it would return an #N/A if it couldn't find an exact match. All sorts of possibilities now...thanks Pete! "Pete_UK" wrote: Lay out your table like this: 0 E 6000 D 8000 C 10000 B 12000 A Suppose this table is in M1:N5, and you enter a sales amount in A1. Put this formula in B1: =VLOOKUP(A1,M1:N5,2) and it will return the appropriate letter. Hope this helps. Pete On Dec 11, 6:57 pm, empire11 wrote: I use basic vlookups all the time so I was definitely thinking of a variation of that. I thought a vlookup had to look up a specific value and find that exact value or the next closest value in a list of data. Maybe there's a different way to do it that I just don't know about? So with my original example, if I did a regular vlookup (as I know it) on $9500, it wouldn't work because $9500 doesn't exist in my lookup table. It's just a point between the min and max of a particular range ($8000-$10000).. ...but that's not what I'm looking for. Plus I'm not looking up nice even numbers, it'll be more like $9543.87 What I want to do is see which range of data a particlar value fits into. Basically I'm trying to assign a Sales Volume to a particular location. A volume is $12000, B volume is between $10000 and $12000, etc. Hopefully that better describes what I'm after... "Pete_UK" wrote: Yes, as long as the data is sorted (as yours is, but it would be easier if it was in the opposite order to what you show), then you could use VLOOKUP. Tell us more about what you want to do. Pete On Dec 11, 4:22 pm, empire11 wrote: Is is possible to look up a value (Sales) in a table of ranges and return another value depending on which range it falls into? Code Sales Min Sales Max A 10000 B 8000 10000 C 6000 8000 D 0 6000 So say I enter 9500 in a cell, I want it to return "B" as 9500 falls witin that range of 8000-12000. I've already created an IF statement that works but as I get more and more ranges, it's going to get confusing and I think it maxes out at 7 conditions. Just thought there might be an alternative to creat a table for the ranges and do some sort of lookup or something. Thanks!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Table
You're welcome - thanks for feeding back.
Pete "empire11" wrote in message ... Yep...that works. I've only ever used lookups where I'm looking for the exact number so alway put =VLOOKUP(A1,M1:N5,2,FALSE) so that it would return an #N/A if it couldn't find an exact match. All sorts of possibilities now...thanks Pete! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Value in Table | Excel Worksheet Functions | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Lookup table help please | Excel Discussion (Misc queries) | |||
Lookup Table help | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |