Multi-Criteria Lookups With INDEX-MATCH

Sometimes a single column does not uniquely identify a row. You might need the price for a product in a specific size, or the salary for an employee in a particular department.

That calls for a multi-criteria lookup: matching on two or more columns at once to pinpoint exactly one row.

INDEX-MATCH handles this elegantly by combining the conditions into a single match test, no extra helper columns required.

The simplest mental model joins your key columns into one. Add a helper column that glues product and size together, then do an ordinary lookup against it.

For example a helper cell might hold =A2&"|"&B2, producing "Shirt|Large". You then MATCH "Shirt|Large" against that combined column.

This works, but it clutters your sheet. The next scenes show how to skip the helper entirely.