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.

--
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

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

--
ray

rayteach wrote:
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.

--
ray

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

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


"Ken Johnson" wrote:

rayteach wrote:
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.

--
ray

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

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


"Biff" wrote:

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

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

--
ray

You're welcome. Thanks for the feedback!

Biff

"rayteach" wrote in message
...
Thank you for your response. It worked!
--
ray


"Biff" wrote:

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

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

--
ray