VLookup + Match functions for two column lookup with multiple words

[liveproper]

Hello,

I am attempting to create a sales quote creator. I have a product database in one sheet with product number, product, product options, and price in each column.
I then have the 'sales quote creator' in another sheet with a dependent dropdown list (with product options being dependent on which product you choose). Of course, as there is a space in the names so I used =INDIRECT(SUBSTITUTE(B6," ","")). (For a more thorough description of how I made a dependent dropdown list with multiple words you can view: http://www.contextures.com/xlDataVal02.html)

My problem is I now want to pull up the price based on the product and product option chosen. I am fairly certain that you can use a combination of the VLookup and Match functions to accomplish this, however I don't know how to make it work with the Indirect function. Maybe you don't even need it. I don't know.

Anyway this is a visual representation of what I am after: dropbox link.

If anything is unclear please let me know. Thanks!!
LP

 

[oldbrewer]

in sheet 2 put this formula

=sumproduct((sheet1!b3:b1000=B6)*(sheet1!c3:c1000=c6)*sheet1!d3:d1000))

this will return $9.00

 

[liveproper]

in sheet 2 put this formula

=sumproduct((sheet1!b3:b1000=B6)*(sheet1!c3:c1000=c6)*sheet1!d3:d1000))

this will return $9.00

Sheet 2 being the product database or the 'sales quote creator'.

 

[liveproper]

in sheet 2 put this formula

=sumproduct((sheet1!b3:b1000=B6)*(sheet1!c3:c1000=c6)*sheet1!d3:d1000))

this will return $9.00

Here's a dropbox link to the workbook. Feel free to manipulate and let met know what you're getting at.

https://www.dropbox.com/s/6hcffw8gc8wadc4/Excel Help - Sales Quote Creator.xlsx

 

[liveproper]

bump. help !

 

[oldbrewer]

=sumproduct((sheet1!b3:b1000=B6)*(sheet1!c3:c1000=c6)*sheet1!d3:d1000)) should be

=sumproduct((sheet1!b3:b1000=B6)*(sheet1!c3:c1000=c6)*(sheet1!d3:d1000))
^
a dreaded typo

just type this formula into your sheet 2