URGENT - Help with choice of formula
 

Hi everybody;
This is my pb. I have a table with Post Code, Literage and Delivery Price
per Liter.
I have another table in which I have the list of post codes and Price per
Liter depending on the volume (ie. less than 100, between 100 to 199, 200 to
299, 300 to 399 and 400 to 499.)
What could I use to have the information at the intersection of these 2
criteria (ie Postcode is SY and literage is between 200 to 299 so the price
per liter is 0.32.
Thanks in advance

Here is A way ...

Set up your table with the first column being PostCode, and the second
to nth columns being your delivery price breaks (I used 0, 1, 10, 100,
1000, 10000). Enter your price break amounts into the table. I made
my table at A5:G5.

Now, if you enter a delivery Post Code in, say, A1, and the delivered
liters in B1, the formula at C1 would be:
=VLOOKUP( A1, $A$5:$G$8, MATCH( B1, $A$5:$G$5, 1), 1)

VLookup looks down the column of PostCodes to find the match to A1,
then
Match looks up the next nearest (or exact) match to B1 for the column
value (the "1" arguments tell Excel it doesn't have to find an exact
match).

Chris

Hi;
The pb I have is that I have 2 worksheets. One with the cell that needs to
be populated automatically, and the other with the postcodes and the prices
per liter depending on the volume. I tried your formula but it did not work.
Sent you an email to see if I could send you an example of the workbook.
Thanks

"Blue Hornet" wrote:

Here is A way ...

Set up your table with the first column being PostCode, and the second
to nth columns being your delivery price breaks (I used 0, 1, 10, 100,
1000, 10000). Enter your price break amounts into the table. I made
my table at A5:G5.

Now, if you enter a delivery Post Code in, say, A1, and the delivered
liters in B1, the formula at C1 would be:
=VLOOKUP( A1, $A$5:$G$8, MATCH( B1, $A$5:$G$5, 1), 1)

VLookup looks down the column of PostCodes to find the match to A1,
then
Match looks up the next nearest (or exact) match to B1 for the column
value (the "1" arguments tell Excel it doesn't have to find an exact
match).

Chris