Multiple look up tables

Good afternoon all

Can someone please help me regarding look up tables.

I have a spreadsheet (attached), which has a product, then the spec then the price Matrix for that product.

I have 12 products in total, each with their own spec and price matrix (Columns A - C).

As this is going to be a daily order sheet for my client, I want to simplify this with a series of drop down boxes for the product (Column P) and spec (Column Q) and then when the enter the quantity, the price to worked out for them (Columns G - S).

I know how to do this if it was just 1 spec, but no idea how to do it with multiple products?

Thank you in advance

Craig Kinsey

i'd do it with tables

Thanks Tim. That is excellent work. I am not sure what columns N & O are for or whether I need these, but the only thing that is missing is the spec (Column Q on my original and Column K on your sheet). This information was under the Product in Column A? Is this an easy add?

=INDEX(s!$A:$A,MATCH([@PRODUCT],_s[[#All],[Print 4/4 on 170gsm silk]],)-1)