INDEX+MATCH return a column from two drop down lists

[mvoils]

I am trying to create a database that will show me a list of motor specifications based on what we have them labeled as and were that unit is located. The data table looks something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Pump Location[/TD]
[TD]Pump Name[/TD]
[TD]HP[/TD]
[TD]Volts[/TD]
[TD]Phase[/TD]
[TD]RPM[/TD]
[TD]Overload[/TD]
[TD]OCPD[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Well 1[/TD]
[TD]Well Pump[/TD]
[TD]2[/TD]
[TD]240[/TD]
[TD]3[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Well 2[/TD]
[TD]Well Pump[/TD]
[TD]5[/TD]
[TD]240[/TD]
[TD]3[/TD]
[TD]7500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Plant 1[/TD]
[TD]Pump 1[/TD]
[TD]10[/TD]
[TD]480[/TD]
[TD]3[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Plant 1[/TD]
[TD]Pump 2[/TD]
[TD]25[/TD]
[TD]480[/TD]
[TD]3[/TD]
[TD]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Plant 1[/TD]
[TD]Pump 3[/TD]
[TD]25[/TD]
[TD]480[/TD]
[TD]3[/TD]
[TD]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Plant 2[/TD]
[TD]Pump 1[/TD]
[TD]50[/TD]
[TD]240[/TD]
[TD]3[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Plant 2[/TD]
[TD]Pump 2[/TD]
[TD]50[/TD]
[TD]240[/TD]
[TD]3[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Plant 2[/TD]
[TD]Pump 3[/TD]
[TD]50[/TD]
[TD]480[/TD]
[TD]3[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The active table looks something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Name[/TD]
[TD]HP[/TD]
[TD]Volts[/TD]
[TD]Phase[/TD]
[TD]RPM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Drop Down Menu[/TD]
[TD]Drop Down Menu Dependent on A2[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The idea is you pick a location from the first drop down menu, then a name from the second drop down menu which is dependent on the first drop down menu, returning only the names that are at that location, and finally you are given a value dependent on both A2 and B2, found on the data table. Pumps are named the same at some locations, and some locations have more than one pumps so I cannot use just the one value, I must use both values.

Is there a way to have MATCH (or similar function) return an array instead of a column value? I could then have INDEX search an array defined by MATCH or the other way around?

 

[Rukt]

Hi mvoils, try this array formula (Ctrl+Shift+Enter) as a starting point:

{=INDEX(C2:C9,MATCH(A13&B13,A2:A9&B2:B9,0),1)}

In my example, A13 and B13 are the two drop-down cells, and it's grabbing a value for horsepower. You'd change the C2:C9 reference to get other motor parameters.

As a side note, if it's not too late, I would strongly advise that you give all the equipment throughout your plant unique names. For instance, PM-W101 for the pump at well 1, and PM-W201 for the pump at well 2. This will make it much easier to do any sort of asset management activities in future!

 

[mvoils]

Thank you for the quick response!

Unfortunately the plant has been in operation for many many years before I came into the picture, and had many many updates and changes. That would be quite the undertaking to rename them all.

=INDEX(MotorData!C2:C150,MATCH(A2&E2,MotorData!A2:A150&MotorData!B2:B150,0),1)

This is the adjusted formula to individualize what you had given me. The Motor Data is on another sheet referenced by the MotorData! C2:C150 covers all of the third column (in this case it is actually Motor Brand Name) A2 and E2 are the two drop down menus, A2:A150 covers the locations, and B2:B150 cover the pump names in the massive data table, both referencing the MotorData sheet. I receive a #VALUE! Error.

Am I interpreting your formula correctly?

 

[mvoils]

I just noticed that I missed the array creation portion of the instructions. Which cells are a part of the array? The list that it is being derived from, or only the cell showing the listed information, or the listed cell and the drop down menus?

 

[Rukt]

Hi mvoils, if I'm interpreting your explanation of the layout of your data correctly, your formula should be correct. The "array creation portion" refers to confirming the formula with Ctrl+Shift+Enter, rather than just Enter. This makes Excel evaluate the formula cell-by-cell, so it will check your location and name against the first pair in the data table, then the second pair, etc.

If that still doesn't do the trick, can you upload your file somewhere like Dropbox, and share the link here?

 

[mvoils]

This has worked perfectly, and I thank you for your assistance and expediency Once I worked out the array it all fell into place - my mistake for skipping the first sentence!

 

[Rukt]

My pleasure.