Automatically populating a value based on several drop down lists

Good afternoon. I'm trying to put together spreadsheet that will populate a value based on the selection of 3 drop down lists. This list is meant to be a quick price reference for a customer based on the selected criteria. Please see the attached example.

For a quick run down....My customer selections are Pipe size, ANSI rating, and Meter type. Based on the selected drop down options I would like for Excel to lookup a price for me. I've started with the 3" option in an attempt to get things rolling, but at this point I'm about to bash my head against the computer.

Any help is much appreciated. Thanks in advance.

So, what you need, which you have partially done, is a table listing all possible combinations of the pipe size, ANSI rating and meter type (as you have done in Sheet1). Then you'll need a lookup of some kind, which I have done in the cells highlighted in yellow (column G). See if this suits your needs. You'll need to extend the references to Sheet1 appropriately as you populate the table of prices.

Quekbc- Thanks for the quick reply.

I have attached a revised spreadsheet to clean up some of the mess on page 2. So what I am looking to do, is make the selections from the drop down menu on pg. 1, and reference the prices I've put on pg. 2. Let's focus only on the 3" with multiple options.P66 LACT and Leak detection example_bc revised.xlsx

You had your price lookup the wrong way round. You want to find the price based on the selections in Sheet1 not the way you had it in Sheet2 which is why you were getting the N/A errors if you changed the drop down values.

Look at Sheet1 under "Price" where I have entered the formula below,

=INDEX(Sheet2!$F$3:$F$8,MATCH($B6&$C6&$D6,INDEX(Sheet2!$C$3:$C$8&Sheet2!$D$3:$D$8&Sheet2!$E$3:$E$8,0),0))

Post script:

Add the ERROR handling so you can drag the formula down as required:

=IFERROR(INDEX(Sheet2!$F$3:$F$8,MATCH($B6&$C6&$D6,INDEX(Sheet2!$C$3:$C$8&Sheet2!$D$3:$D$8&Sheet2!$E$3:$E$8,0),0)),"")