Index and match on multiple columns

To lookup a value by matching across multiple columns, you can use an array formula based on several functions, including MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0))} where "names" is the named range C4:E7, and "groups" is the named range B4:B7. The formula returns the group that each name belongs to. Note: this is an array formula and must be entered with control shift enter.

April 14, 2025 · 1 min · 111 words · Rachel Bowman

Raising the result to the power of zero simply converts all numbers in the array to 1.

The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2.

COLUMN Function

The Excel COLUMN function returns the column number for a reference.

Excel formula: Count rows with at least n matching values

For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.

When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.

Excel formula: Count rows with multiple OR criteria

The MATCH function is often used together with INDEX to provide row and column numbers….

Excel formula: Count if row meets internal criteria

Excel formula: Count if row meets multiple criteria

Excel MMULT function

Excel COLUMN function

Excel TRANSPOSE function

Excel INDEX function

COLUMN Function#

COLUMN Function