look-up in a range
 

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!

look-up in a range
 

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)

--
Biff
Microsoft Excel MVP


"Jimmy" wrote in message
...
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!

look-up in a range
 

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"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jimmy" wrote:

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!

look-up in a range
 

Hi,
=IF(A131,B8,IF(AND(A115,A1<31),B7,B6))

"Jimmy" wrote:

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!

look-up in a range
 

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jimmy" wrote:

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!

look-up in a range
 

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 C1:D30 then

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

or the array equivalent

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

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jimmy" wrote:

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!