Vlookup from table based on variable weight range

[meksdigital]

Hi all and HNY!

I have a table with prices based on weights and but having trouble trying to return the price value into another sheet depending on the weight.

Rates Table

Weight >	Weight <=	Price
0​	5​	$8​
5​	10​	$16​
10​	20​	$22​

Items with weight

Items	Weight	Price
Item 1		?
Item 2		?
Item 3		?

Any thoughts on how I can get the table data into my other table? Having lots of trouble trying with vlookups + less thans to pull in prices

Cheers all

 

[Legacy 143009]

In fact, this is a perfect scenario to use VLOOKUP's close match=TRUE. 'Items with weight' C2:

=VLOOKUP(B2,'Rates Table'!$A$2:$C$4,3,TRUE)

 

[meksdigital]

In fact, this is a perfect scenario to use VLOOKUP's close match=TRUE. 'Items with weight' C2:

=VLOOKUP(B2,'Rates Table'!$A$2:$C$4,3,TRUE)

Thanks for the reply....

Weights could, but will probably not be one of the specificed values.... eg. weight could be between 0 and 5, or between 5 and 10

edit* will update table above... would close match true work under these circumstances?

Thanks!

 

[meksdigital]

Rates Table

Weight >	Weight <=	Price
0​	5​	$8​
5​	10​	$16​
10​	20​	$22​

Items with weight

Items	Weight	Price
Item 1	3.5	?
Item 2	7	?
Item 3	12.2	?

 

[Legacy 143009]

I think it works quite OK.

 

[HongRu]

It's strange.
Your "0" is not a "0" but a "0" and a unicode(8203).
The same to 5, 10, 20, $8...。

So if you can't vlookup well, maybe try to key-in again all the numbers in the rates table.

 

[Peter_SSs]

I think it works quite OK.

I'm not so sure. From my reading of the Rates table a weight of 5 should return 8 not 16 per your formula in column D below.

@meksdigital
My suggestion is in column C

23 01 09.xlsm
	A	B	C	D
1	Items with weight
2	Items	Weight	Price
3	Item 1	3.5	8	8
4	Item 2	7	16	16
5	Item 3	12.2	22	22
6	Item 4	5	8	16
Results

Cell Formulas
Range	Formula
C3:C6	C3	=XLOOKUP(B3,'Rates Table'!B$2:B$4,'Rates Table'!C$2:C$4,,1)
D3:D6	D3	=VLOOKUP(B3,'Rates Table'!$A$2:$C$4,3,TRUE)

23 01 09.xlsm
	A	B	C
1	Weight >	Weight <=	Price
2	0	5	$8
3	5	10	$16
4	10	20	$22
Rates Table

 

[Alex Blakenburg]

It's strange.
Your "0" is not a "0" but a "0" and a unicode(8203)..

A bit off topic but when the OPs copy data directly into the post it is quite common for it to pick up a character 8203 somewhere along the line.
I keep a little Replace ChrW(8203) macro in my personal workbook to remove it when it happens.

 

[Legacy 143009]

Yes,

A bit off topic but when the OPs copy data directly into the post it is quite common for it to pick up a character 8203 somewhere along the line.
I keep a little Replace ChrW(8203) macro in my personal workbook to remove it when it happens.

It's strange.
Your "0" is not a "0" but a "0" and a unicode(8203).
The same to 5, 10, 20, $8...。

So if you can't vlookup well, maybe try to key-in again all the numbers in the rates table.

Happened the same to me.