VLookup

[Guest]

Hi people much smarter than me!
I was hoping someone could help me!

I have a cell containing a product weight, in one sheet.
I have a table in a different sheet consisting of 2 columns:

Weight Not Over (grams) Mailing Price
60 £0.31
100 £0.48
150 £0.66

What I am trying to achieve is, when my cell containing product weight has a
value i want to look in my table and return the correct mailing price.

I have used the Vlookup function to get as far as i have, but i am not
getting the results i expect.

For my example my Product weight is 65 so the value i am looking to be
returned is £0.48. Unfortunately the result I am having returned is £0.31!

Here is my formula:
=VLOOKUP(G3,'Royal Mail Lookup Table'!A2:B33,2)

Where G3 is my Product weight of 65 and A2:B33 is my table.

The Weight Not Over column in my table is obviously a group of numbers, and
i can't for the life of me figure out what i need to do to make my formula
return the correct result! I can't work out the boundarys in my weight not
over column as if my product weight was 60.00001 it would still have to
return £0.48.

I know that if the range_lookup is TRUE or omitted, an exact or approximate
match is returned. If an exact match is not found, the next largest value
that is less than lookup_value is returned. Which won't really return the
value that i want... but how would i get the formula to return a value less
than or equal to the group that is weight not over column is bound by.

Any help would be greatly appreciated!

 

[Bryan Hessey]

As a table, you could use:

From Weight Not Over (grams) Mailing Price
1 60 £0.31
61 100 £0.48
101 150 £0.66

and lookup on the From price, selecting column 3

Hope this helps

 

[BruceP]

The LOOKUP() function should do exactly what you want. For the matching
criteria, simply refer to the appropriate cells in your table - that
way, you don't have to keep changing values all over the place if your
weights or rates change.