Search for specific number in a range

[Ruan]

Good day to all
I'm having trouble figuring out which function i should use to find a
certain range of numbers.
First, i'll explain my problem thoroughly:
In one column I have ranges of numbers filled in cells (example: cell 1: 0 -
50 kgs; cell 2: 51 - 100 kgs; cell 3: 101 - 200 kgs;...) and in the adjacent
column I have the prices which go with the specific weight range. I worked
out this formula:

=VLOOKUP(A2;'Hong Kong seafreight'!A32:B58;2)

in which A2 equals the weight of the cargo entered by a user, so for example
the user enters a weight of 49 kgs, the function has to display the correct
price. A32:B58 is my range in which all my data is contained, and "2" is the
column in which excel can find the prices associated with the range in weight.

Second, my problem:
For example, when i enter a weight of 502 kgs it gives me the price of the
weight category between 5001 - 6000 kgs. But i need the price of the weight
category between 501 - 600 kgs. Can anyone help me point the right direction
to search?

 

[Mike H]

Hi,

Create you table of weight/costs in A32 - B58 looking like this

0 10
51 20
101 30
201 40
301 50
401 60
501 70
etc
Note the table must be sorted by column A
Then use the formula
=VLOOKUP(A2,'Hong Kong seafreight'!A32:B58,2,TRUE)

Mike