Forum Discussion
Index Match Multiple Criteria
I am a newbie and I understand the general index match lookups. However I am struggling with a more complicated set of information that needs an index match but with applicability criteria.
Example as follows (formula to be built in cell L4):
1. Lookup material description Bracket in column B (there are 3 bracket options)
2. Lookup columns C to J to find which bracket option is applicable to product ID AA1 (it is the first bracket option where model code AA1 takes 1 bracket)
3. Return the material code for this match (in this case material code 123A-1)
Zaid999 You don't mention which Excel version you are using, so I came up with a few solutions. See if any of these suits you. I'm quite sure that there are other options to achieve this, but three will do for me right now.
Edit: Hadn't seen HansVogelaar 's post before I posted mine. Now you have more to choose from.
- Many thanks for the response and also for the various options (very thoughtful)
I use Excel 365
In L4:
=INDEX($A$5:$A$150,SUM(($B$5:$B$150=M4)*($C$4:$J$4=N4)*($C$5:$J$150>0)*(ROW($A$5:$A$150)-ROW($A$5)+1)))
Adjust the ranges if needed, then fill down.
- Many thanks - it works very well
