Thread: VLOOKUP in a range

Dear all,

I have been using Excel to calculate some prices based on the weight of the product. However the problem is that the prices are based on brackets. This means that for e.g. weights ranging from 1000 to 1999 lbs the price is 10 per lbs, for 2000 to 3999 its 20 lbs etc. The prices do not increment with a fixed range.

I imagine that VLOOKUP is the thing to use here, the problem is that it can only lookup one value. Thus if I would want to make this work I have to create a table which links 1000 to 10, 1001 to 10, 1002 to 10. This would be doable for lets say 50 rows but this specific sheets needs to support at least weights of 12000 lbs so that would give us 12000 rows.

I have attached an example of how my table is constructed.

Thanks in advance,

Stijn