Populating a drop down list dependent on multiple matching variables

**Sardaukar** · 02-27-2013, 05:16 PM

Attached is my workbook.

Basically I'm trying to populate D17:D21 with drop down lists that are dependent on drop down lists in $A$14, B17:B21, C17:C21 respectively. The data I'm trying to index and match based on the variables is on the worksheet "Compressor Details". I have named the appropriate ranges to check the variable $A$14 "Compressor_Make" B17:B21 "Compressor_BTU" C17:C21 "Compressor_Zones" Respectively. The formulas that I have in D17:D21 data validation list right now semi-works in that it returns the first value that matches. How do I get the drop down list populated with all matches?

Also, I tried an Index Match formula for F17 and I went through the calculation steps and I can't figure out why the nested match function doesn't work.

Best Regards and Many Thanks,

(UPDATE):

I've gotten the Index/Match formula to work for F17 etc. But I still can't get the list to populate with more than the first match. Any Ideas? The workbook has been updated

Sard

**Sardaukar** · 02-27-2013, 08:05 PM

Any Help? I'm having trouble finding anything about this online.

**shg** · 02-27-2013, 08:32 PM

See http://www.contextures.com/xlDataVal02.html

**Sardaukar** · 02-27-2013, 08:38 PM

Thanks, I'll go through that.

**Sardaukar** · 02-27-2013, 09:50 PM

Ok. I went through that walkthrough.

I got the walk-through to work up to a point. The problem that I have, and the problem that the method you linked shg, is that my data has multiple entries with the same name and numerical value for two attributes of a product, but a 3rd unique numerical attribute that determines the model and other aspects.

So for instance. I could have 3 Fujitsu AC compressors, each can cool/heat 9000 BTU/h, and each can cool one zone, but they all have different seer efficiencies, and that alone differentiates the model in terms of price, accessories, power usage etc. Using the Master-ValData-Counter-Uselist method works with unique variables for each category, but when multiple categories have the exact same variable, it can't seem to distinguish them apart, regardless of what was selected in the "parent" drop down lists. As a result, I can have drop down lists that accurately list the make, and the BTU/h, but then it lists all possible zones offered by that make regardless of the BTU specified.

This is frustrating. Any ideas?

**Sardaukar** · 02-28-2013, 02:48 AM

I got it!

This was the formula I used: =IF($B14="","Enter Desired BTU",OFFSET(INDEX(Compressor_Efficiency, MATCH(1,($B$12=Compressor_Make)*($B14=Compressor_BTU)*($C14=Compressor_Zones),0)),,0,SUMPRODUCT(($B$12=Compressor_Make)*($B14=Compressor_BTU)*($C14=Compressor_Zones))))