Create a table where the row headers are the weight intervals and the column
headers are the zones (or vice versa). Then, you'd look for the intersection
of the weight and zone to get the price.

Here's a small file that demonstrates this.

2D lookup.xls 16kb

http://cjoint.com/?cxt7775H1q

One thing I noticed in your formula is that you're excluding several
possible weights:

<=500...501...that excludes 501
<=2000...2001...that excludes 2001
<=3000...3001...that excludes 3001
etc
etc

--
Biff
Microsoft Excel MVP


"Emmaly" wrote in message
...

Dear All,
I am writing a massive IF statement and i have nearly got it to work but
i need help with the last bit.
I have a got a price list for shipping with weight brackets and
different prices, so far i have written the IF so that it looks to see
how many kilos i am shipping and then it looks on the price list and
then calculates the correct shipping cost.
So far this works.. see IF below

=IF(M2<=500,'UK rate 2010'!B$9*M2/1000,IF(AND(M2501,M2<=2000),M2*'UK
rate 2010'!C$9/1000,IF(AND(M22001,M2<=3000),M2*'UK rate
2010'!E$9/1000,IF(AND(M23001,M2<=4000),M2*'UK rate
2010'!F$9/1000,IF(AND(M24001,M2<=5000),M2*'UK rate
2010'!G$9/1000,IF(AND(M25001,M2<=7500),M2*'UK rate
2010'!H$9/1000,IF(AND(M27501,M2<=10000),M2*'UK rate
2010'!I$9/1000,IF(AND(M210001,M2<=12500),M2*'UK rate
2010'!J$9/1000,IF(AND(M212501,M2<=15000),M2*'UK rate
2010'!K$9/1000,IF(M215001,M2*'UK rate 2010'!L$9/1000,))))))))))

But the problem is that there are 8 different shipping zones, so i need
it to say if cell C2 says zone 1 look at the prices in row 9 on the 'UK
rate 2010' sheet if C2 says zone 2 look at row 10 etc etc which i don't
think it can do.

I was wondering if i can copy the above formula to somewhere else in the
sheet (cell C100 for example) and do an IF that says if C2 = zone 1
insert formula in cell C100 If it says zone 2 insert formula in C101 and
i do an IF for each shipping zone. The thing is when i tried this
because the formula was looking at cell M2 it only ever looked at that
cell i couldn't get it to understand that the M is fixed but it needs to
replace the row number to which ever number it has inserted to.

Sorry this is such a rambling question but i feel like i am so close i
just can't quite work out the last bit!!

Please help!!! Thank you Emmaly




--
Emmaly