Excel Functions: INDEX (To look up a value based on multiple criteria in separate columns)

When to use INDEX function?

VLOOKUP function only allows you to look up a value based on single criteria.

To look up a value based on multiple criteria in separate columns, use this generic formula:

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Where:

• Return_range is the range from which to return a value.
• Criteria1, criteria2, … are the conditions to be met.
• Range1, range2, … are the ranges on which the corresponding criteria should be tested.

Important note!

This is an array formula and it must be completed with Ctrl + Shift + Enter. This will enclose your formula in {curly brackets}. Typing {} manually will not work so do not waste your time in doing so!

Example

=INDEX(Legacy!F2:F290, MATCH(1, ([@[Control ID]]=Legacy!C2:C290) * ([@Application]=Legacy!E2:E290), 0))

This formula look ups value from the Legacy sheet on the same workbook only if the “Control ID” and “Application” value are matched on both sheets.

References

Excel INDEX MATCH with multiple criteria — formula examples (ablebits.com)