VLOOKUP function returning data from ranges

[rayteach]

I want to use a VLOOKUP function to find data in a table that includes
ranges. Example:

# of tables Cost per table
1 – 19 $50.00
20 – 39 $45.00

If I then type 26 in a cell, $45.00 would appear. I can then have a simple
multiplication formula in the next cell to calculate total cost.

 

[Biff]

Setup your table like this:

List the lower boundary of each range in column B:

..............B...............C
1...........0...............0
2...........1..............50
3..........20.............45

A1 = 26

=VLOOKUP(A1,B1:C3,2)

Or:

=LOOKUP(A1,B1:B3,C1:C3)

Biff

 

[Ken Johnson]

I want to use a VLOOKUP function to find data in a table that includes
ranges. Example:

# of tables Cost per table
1 - 19 $50.00
20 - 39 $45.00

If I then type 26 in a cell, $45.00 would appear. I can then have a simple
multiplication formula in the next cell to calculate total cost.

Hi Ray,

If the leftmost column of the lookup table only showed the upper limit
of the range (19, 39 etc) then you could use =INT(A1/20)*20 + 19 (where
the value in A1 is the lookup value) to convert the lookup value to the
appropriate range upper limit. You could include and hide a column of
range upper limit values.

Ken Johnson

 

[rayteach]

Thank you Ken for your reply. I had used the reply by Biff as that worked.

 

[rayteach]

Thank you for your response. It worked!
--
ray

Setup your table like this:

List the lower boundary of each range in column B:

..............B...............C
1...........0...............0
2...........1..............50
3..........20.............45

A1 = 26

=VLOOKUP(A1,B1:C3,2)

Or:

=LOOKUP(A1,B1:B3,C1:C3)

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thank you for your response. It worked!