Vlookup with multi dropdowns and tables

[SWHarmon79]

Dropdown list	Item	Lookup value (price)
Dropdown list	Item	Lookup value (price)
Dropdown list	Item	Lookup value (price)
		Total

I have an estimating spreadsheet that I built and have used for years that worked great but now I have to make some changes as it has grown considerably. I used to be able to keep everything on one table and use vlookup to find my values but now I need to separate things by categories such as conduit, wire, boxes etc... I need to update the existing formula from this =IFERROR(VLOOKUP(D5,'Labor Unit & Material Pricing'!$A$5:$D$1878,2,0),"") to a formula that will change the lookup table based on the dropdown selection. The above table is a simple representation of what the table looks like.

As an example based on the above table

Conduit	1/2" EMT	25.00
Wire	#12 THHN	.26
Boxes	4sq	1.00
		26.26

I also added a screenshot of one of the actual tables.

Thanks in advance for any assistance!

 

[SWHarmon79]

Anyone? I found a work around but would really like to figure out how to accomplish this as described. Thanks

 

[Toadstool]

Hi SWHarmon79,

If your sheet "Labor Unit & Material Pricing" looks like this:

Book1
	A	B	C
4	Category	Item	Price
5	Conduit	1/2" EMT	25
6	Wire	#12 THHN	0.26
7	Boxes	4sq	1
Labor Unit & Material Pricing

Then this will work for your lookup:

Book1
	C	D	E
5	Wire	#12 THHN	0.26
6	Boxes	4sq	1
7	Conduit	1/2" EMT	25
Sheet1

Cell Formulas
Range	Formula
E5:E7	E5	=INDEX('Labor Unit & Material Pricing'!C$5:C$1878,MATCH(1,INDEX((C5='Labor Unit & Material Pricing'!$A$5:$A$1878)*(D5='Labor Unit & Material Pricing'!$B$5:$B$1878),0,1),0))