Multi level lookup based on dependent dropdowns.

Capture.PNG

New to the forum so sorry if this is in the wrong spot! Sample workbook has been added to attachments.

It is exhausting constantly looking through price books so was wanting to generate an easier way to lookup prices based on drop down (data validation) criteria.

Drastically reduced the size for this example, total sheet is >8000 cells deep. Also the descriptions need to be cleaned up but not focused on that right now.

Ideally I'd like to have the first dropdown list:
BLKPIPE
FITTINGS - BRASS
FITTINGS - STAINLESS

If "BLKPIPE" is selected in the first then I'd like it to generate the below in the next drop down:
PIPE
BUSHING
FLANGES

If "PIPE" is selected then the next dropdown will populate:
1/2" PIPE
3/4 PIPE
1 1/2 x 2
2 x 8

If "1/2" pipe is selected then generate price of .77 I'll then take that cell to an estimate page and generate a total price based on "qty" or Length"

I've done a lookup before with combos of "if, match, offset" statements but the sheer size of this thing is daunting. May even be in a VBA range which I'm not too familiar with.

For the most part it is a 3 description path to get to the price but a few oddballs have 4 and a single one has 5.

Any easy way to do this?

The following changes the format of the data and utilizes three types of data validation.
1. The data is formatted into records with each row containing the three descriptions and a price without blanks spaces
2. The data validation for Description 1 is: =$L$2:$L$4
3. The data validation for Description 2 is: =INDIRECT(SUBSTITUTE(G$2," - ",""))
4. The data validation for Description 3 is: =OFFSET(C$1,AGGREGATE(15,6,(ROW(C$2:C$40)-ROW(C$1))/(A$2:A$40=G$2)/(B$2:B$40=H$2),1),0,COUNTIFS(A$2:A$40,G$2,B$2:B$40,H$2))
5. The formula to find the price is: =INDEX(D2:D40,AGGREGATE(15,6,(ROW(D2:D40)-ROW(D1))/(A2:A40=G2)/(B2:B40=H2)/(C2:C40=I2),1))
Let us know if you have any questions.