XLOOKUP match any column

To perform a lookup by matching a value in any one of several columns, you can use a formula based on the XLOOKUP function together with the MMULT function. In the example shown, the formula in K6 is: =XLOOKUP(1,MMULT(--(codes=K5),SEQUENCE(COLUMNS(codes),1,1,0)),group) where codes (C5:H15) and group (B5:B15) are named ranges. The result for the lookup code "BDBC" is "Epsilon. This lookup can also be done in older versions of Excel with INDEX and MATCH, as explained below.

April 14, 2025 · 1 min · 95 words · Michael Thomas

Table of Contents

The result is an array of 11 rows by 6 columns.

Thecolumnsargument is 1,startis 1, and thestepvalue is zero.

Note that the range C4:H4 could be anamed range, and can contain values of any kind.

Excel formula: Get column totals

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

SEQUENCE Function

The Excel SEQUENCE function generates a list of sequential numbers in an array.

The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.

Excel formula: Get row totals

…

COLUMNS Function

The Excel COLUMNS function returns the count of columns in a given reference.

Excel formula: Get location of value in 2D array

Excel MMULT function

Excel SEQUENCE function

Excel COLUMNS function