Lookup Table

[empire11]

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!

 

[Pete_UK]

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

 

[empire11]

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]

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

 

[empire11]

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]

You're welcome - thanks for feeding back.

Pete