software

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!


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

--Alex Brown Wrote:
gt; Hi people much smarter than me!
gt; I was hoping someone could help me!
gt;
gt; I have a cell containing a product weight, in one sheet.
gt; I have a table in a different sheet consisting of 2 columns:
gt;
gt; Weight Not Over (grams) Mailing Price
gt; 60 £0.31
gt; 100 £0.48
gt; 150 £0.66
gt;
gt; What I am trying to achieve is, when my cell containing product weight
gt; has a
gt; value i want to look in my table and return the correct mailing price.
gt;
gt; I have used the Vlookup function to get as far as i have, but i am not
gt; getting the results i expect.
gt;
gt; For my example my Product weight is 65 so the value i am looking to be
gt; returned is £0.48. Unfortunately the result I am having returned is
gt; £0.31!
gt;
gt; Here is my formula:
gt; =VLOOKUP(G3,'Royal Mail Lookup Table'!A2:B33,2)
gt;
gt; Where G3 is my Product weight of 65 and A2:B33 is my table.
gt;
gt; The Weight Not Over column in my table is obviously a group of numbers,
gt; and
gt; i can't for the life of me figure out what i need to do to make my
gt; formula
gt; return the correct result! I can't work out the boundarys in my weight
gt; not
gt; over column as if my product weight was 60.00001 it would still have
gt; to
gt; return £0.48.
gt;
gt; I know that if the range_lookup is TRUE or omitted, an exact or
gt; approximate
gt; match is returned. If an exact match is not found, the next largest
gt; value
gt; that is less than lookup_value is returned. Which won't really return
gt; the
gt; value that i want... but how would i get the formula to return a value
gt; less
gt; than or equal to the group that is weight not over column is bound by.
gt;
gt; Any help would be greatly appreciated!--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534935
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.--
BruceP
------------------------------------------------------------------------
BruceP's Profile: www.excelforum.com/member.php...oamp;userid=33653
View this thread: www.excelforum.com/showthread...hreadid=534935