glass pricing sheet, matching an m2 area to a price range

[tantonar]

I hope I can explain this well enough.

I have a price list from a supplier of sheet materials. The price list is "size not exceeding", for example upto 0.3m2 =£50.00ea, from 0.301m2 > 0.4m2 =£59.00 and so on.

When I am pricing a job I input the width and the height, then an area is calculated. I need excel to find this area with the range set on another sheet, and insert the price into the original sheet.

Eg: 0.850 x 0.500 = 0.425m2, this is between the 0.401 > 0.500 range, excel returns the price from the "not exceeding 0.5m2" value

Thanks in advance.

[oldchippy]

Hi,

Does this lookup table help?

[tantonar]

That is exactly what I wanted, thank you so much!

I have added another table so that I can switch between two price lists. Is there a better way of doing that as the function seems a tad messy!

Ashley

[oldchippy]

An easy way would be to type 1 in H1 and 2 in L2, then select C2, go to Data > Validation > Settings > List and type in the Source box 1,2 with no space between

Change your formula to

=IF(C2=1,IF(D2="","",IND.........