Lookup multiple conditions to return one result

[cchrisj]

I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.

So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.

etc.

I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.

Any ideas? Thanks.

[NBVC]

Try

=Index(Z1:Z100,Match(1,Index((X1:X100=A1)*(Y1:Y100=B1),0),0))

where table is in X1:Z100 and drop downs are in A1 and B1....

[cchrisj]

Hmmm. Can't seem to get it to work.

I've attached my spreadsheet. The "print size" and "media" are the two criteria that detemine the pricing (see table to right). I have pasted your formula in L23 (highlighted).

[NBVC]

Try:

=INDEX(N6:N18,MATCH(1,INDEX((L6:L18=E16)*(M6:M18=F16),0),0))