Index Match with multiple criteria to match on VL table.

[HenryF234]

Good afternoon fellow Excel friends!!

First post on this forum but here goes,

I am trying to search and get a result in a VL Table based on multiple criteria that need be meet that will determine the result

I have tried to do a VLookup and a few other ways but I am unable to get it to be correct since it stops at the first correct result and gives the answer.

Have also tried index match but it doesnt like the blanks

Any help? I submitted the sample spreadsheet but the desired result that i am trying to get with a formula.

[GeoffW283]

Just to clarify:

"Model Data" must match "Assembly"

"Tire Code" must match "Choice code"

But how do "Axel code" and "Coupler code" work? They both seem to appear only in "VL-Table" "Second" (col-C).

The "Third" column seems to be entirely blank - does that column come into play at all?

[HenryF234]

Just to clarify:

"Model Data" must match "Assembly"

"Tire Code" must match "Choice code"

But how do "Axel code" and "Coupler code" work? They both seem to appear only in "VL-Table" "Second" (col-C).

The "Third" column seems to be entirely blank - does that column come into play at all?

"Model Data" must match "Assembly" Yes that is correct

"Tire Code" must match "Choice code" Yes that is also correct

But how do "Axel code" and "Coupler code" work? They both seem to appear only in "VL-Table" "Second" (col-C). So every model detail receives an axle and coupler code regardless if it makes a difference on the Tire code. Either of them can be used in the "Second" to configure a different result.

The "Third" column seems to be entirely blank - does that column come into play at all? Not in this instance but it does come into play on occasion

[GeoffW283]

If I just match Model Detail and Tire code then I get your expected results with the exception of row-9 DDX9620 where with tire code T26 the ONLY component number in your VL Table data is 22015 so I don't understand where your expected result of 22018 comes from - please explain.

By the way - welcome to the forum

[HenryF234]

Thanks I am glad to be here, appreciate your help on this.

On that model the Tire codes and Couplers codes together determine what the tire code will be.

[GeoffW283]

On that model the Tire codes and Couplers codes together determine what the tire code will be.

I think you are referring to my post #3 question. The issue is that for DDX9620 and a Tire Code of T26 and a Coupler Code of C17 the only matching row in "VL Table" is row 204 which has a component number of 22015 contrary to your expected result of 22018. So was this just a mistake in your expected results or are we missing something? If we are missing something then what row in "VL Table" should match in this particular case and why. If it is just a mistake in your expected results then either of the formulas that Greg summarizes in his post #5 should work for you, but please confirm either way.

Happy Thanksgiving!

[HenryF234]

I think you are referring to my post #3 question. The issue is that for DDX9620 and a Tire Code of T26 and a Coupler Code of C17 the only matching row in "VL Table" is row 204 which has a component number of 22015 contrary to your expected result of 22018. So was this just a mistake in your expected results or are we missing something? If we are missing something then what row in "VL Table" should match in this particular case and why. If it is just a mistake in your expected results then either of the formulas that Greg summarizes in his post #5 should work for you, but please confirm either way.

Happy Thanksgiving!

Happy Thanksgiving to you as well GeoffW283!!

Thank you for your reply, and yes that is correct. I made a mistake on the sample sheet.

Let me try the formula stated in #5 and verify.

[GeoffW283]

OK, while waiting for feedback on posts #2 and #3, I'll take a guess:

• Assembly must match Model detail
• Choice code must match Tire code
• "Second" must match either Axle Code or Coupler code or be blank

With those assumptions, in Data table cell E3 and copied down:

=INDEX('VL Table'!$E$2:$E$2000, MATCH(1, ('VL Table'!$A$2:$A$2000=A3)*('VL Table'!$B$2:$B$2000=B3)      *(('VL Table'!$C$2:$C$2000=C3) +  ('VL Table'!$C$2:$C$2000=D3) + ('VL Table'!$C$2:$C$2000="")), 0))

Note this is an array formula but as you have O-365 that shouldn't matter to you.

The QTY column F formula is the same as the above except it indexes into col-F rather than col-E

Let us know whether this guess is what you are looking for. If not then please address the questions in posts #2 and #3.

[Gregb11]

Another option, as long as there is only one answer per row, is to use this one formula in E3 and it will give you both Tires and Qty columns.

=FILTER('VL Table'!$E$2:$F$1544,($A3='VL Table'!$A$2:$A$1544)*($B3='VL Table'!$B$2:$B$1544)*(($C3='VL Table'!$C$2:$C$1544)+($D3='VL Table'!$C$2:$C$1544)+('VL Table'!$C$2:$C$1544="")))

Then copy down.

Also, just with minor changes (relative vs. absolute) to Geoff's formula for column E, this can then be copied directly into column F:

=INDEX('VL Table'!E$2:E$2000, MATCH(1, ('VL Table'!$A$2:$A$2000=$A3)*('VL Table'!$B$2:$B$2000=$B3)*(('VL Table'!$C$2:$C$2000=$C3) + ('VL Table'!$C$2:$C$2000=$D3) + ('VL Table'!$C$2:$C$2000="")), 0))

[GeoffW283]

Also, just with minor changes (relative vs. absolute) to Geoff's formula for column E, this can then be copied directly into column F:

Thanks Greg for the formula improvement. Why didn't I see that?

[HenryF234]

As far as I can tell this is working correctly.

[GeoffW283]

Good! Glad we could help and thanks for the feedback.