look-up in a range

[Jimmy]

A1 is a user-entered value for # of employees.

I have a 35 row tiered rate sheet based on number of employees. What
is the best way to look-up A1 and match it to the correct range-row.

Ex:

A1=33

1-10 $5
11-15 $8
16-30 $12
31-50 $15

Thanks!

 

[T. Valko]

Setup your table so that the leftmost column contains the *lower boundary*
of the tier:

...........A..........B
10......1...........5
11......11.........8
12......16.........12
13......31.........15

Then...

A1 = 33

=LOOKUP(A1,A10:B13)

 

[Luke M]

In your table (which I'll assume is A2:B5), change the left column to these
values:

1
11
16
31

Now your lookup formula is simply:
=LOOKUP(A2,A2:B5)
You don't state what you want to happen though if its outside those ranges.
You could change your table to this to accomodate:

0 "Too small"
1 $5
11 $8
16 $12
31 $15
51 "Too big"

 

[Eduardo]

Hi,
=IF(A1>31,B8,IF(AND(A1>15,A1<31),B7,B6))

 

[Shane Devenshire]

Hi,

I like the LOOKUP suggestion best, but if you don't want to create a lookup
table then you could use

=LOOKUP(A1,{1,5;11,8;16,12;31,15})

and the reason to use LOOKUP instead of VLOOKUP, which also works, is that
your first column, the lookup column is sorted ascending so you get away with
a shorter formula. The VLOOKUP formula would be

VLOOKUP(A1,D7:E10,2)
or
=VLOOKUP(A1,{1,5;11,8;16,12;31,15},2)

 

[Shane Devenshire]

Hi,

I ment to add this to my previous discussion. Suppose you want to keep your
lookup table with the entries like 1-10 in the first column. Suppose your
lookup table is in the range C130 then

=SUMPRODUCT(LOOKUP(A1,--LEFT(C1:C30,FIND("-",1:C30)-1),D130))

or the array equivalent

=LOOKUP(A1,--LEFT(C1:C30,FIND("-",1:C30)-1),D130)

array - means you must press Shift+Ctrl+Enter to enter the formula