use IF/Lookup function in multiple criteria

Hi

I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price)
And I have table transaction than have wide, thick, long and price)

I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )

excelformula.gif

pricing.xls

Any help would be appreciated... Thanks

If I've understood...

Please Login or Register  to view this content.

You could use Sumproduct also but this would be prone to error should you have 1+ instances where all criteria are met (ie the Prices would be summed)... the LOOKUP approach in such instances would return the last price.

Where no matches are found as is the case with row 28 you will get an #N/A error... to handle this and to avoid double evaluating what is a slow formula with an ISERROR/ISNA test I would myself opt for:

Please Login or Register  to view this content.

This approach will return 0 where no Price is found.