XLOOKUP match any column

In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and potentially more columns in larger sets of data. 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.

At a high level, this formula uses the XLOOKUP function to perform the lookup, with the number 1 as the lookup value, and the named range group as the return array. The tricky part of the formula is the lookup array, which is created with the MMULT function like this:

MMULT(--(codes=K5),SEQUENCE(COLUMNS(codes),1,1,0))

The MMULT function performs matrix multiplication, which is a handy way to reduce results from many columns to a single column of results. MMULT takes two arrays, array1 and array2, and requires that the number of columns in array1 be the same as the number of rows in array2. The resulting matrix (which is an array) will have same number of rows as the first matrix, and the same number of columns as the second matrix.

The first array is simply the logical test of all codes in the named range C5:I13 against the code we are looking for in K5:

=MMULT(--(codes=K5)

The double negative forces TRUE and FALSE values to 1s and 0s. The result is an array of 11 rows by 6 columns. Notice the lone 1 in row 5 is the only matching code:

{0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,1,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0;
0,0,0,0,0,0}

The first array has 6 columns, so the second array must contain 6 rows. The SEQUENCE function provides an easy way to construct this array, with some help from the COLUMNS function:

SEQUENCE(COLUMNS(codes),1,1,0)

COLUMNS returns 6 to SEQUENCE for as the rows argument. The columns argument is 1, start is 1, and the step value is zero. The result is an array with 6 rows and 1 column, filled only with 1:

{1;1;1;1;1;1}

The MMULT function then calculates the matrix product of the two arrays and returns an array with 11 rows and 1 column:

{0;0;0;0;1;0;0;0;0;0;0}

Notice row 5, which contains the code "BDBC" is 1, while all other rows are zero. This array is returned to XLOOKUP as the lookup_array:

=XLOOKUP(1,{0;0;0;0;1;0;0;0;0;0;0},group)

and XLOOKUP matches the 1 and returns the 5th item in group, "Epsilon"

Get matching column

The formula above can be adjusted to return the position or heading of the matching column. To get the matching column number from the range C4:H4, you can use a formula like this:

=XLOOKUP(1,MMULT(SEQUENCE(1,ROWS(codes),1,0),--(codes=K5)),C4:H4)

The structure of this formula is similar to the formula above. The lookup_value is 1, and the lookup_array is created with MMULT here:

MMULT(SEQUENCE(1,ROWS(codes),1,0),--(codes=K5))

Notice in this case we are using the ROWS function in the first argument to MMULT, and SEQUENCE is configured to create columns. The result from SEQUENCE is an array with 11 columns like this:

{1,1,1,1,1,1,1,1,1,1,1}

The second array in MMULT is created with the same code we used above:

--(codes=K5)

After MMULT runs, it returns the following array directly to the XLOOKUP function as the lookup_array:

{0,0,1,0,0,0}

Notice the 1 corresponds to column 3 in codes. We can now simplify the formula as follows:

=XLOOKUP(1,{0,0,1,0,0,0},C4:H4)

XLOOKUP locates the 1 in the array, and returns the third value in the range C4:H4, which is 3. Note that the range C4:H4 could be a named range, and can contain values of any kind.

Without XLOOKUP and SEQUENCE

In versions of Excel without XLOOKUP and SEQUENCE, this problem can be solved with a formula based on INDEX and MATCH:

{=INDEX(group,MATCH(1,MMULT(--(codes=K5),TRANSPOSE(COLUMN(codes)^0)),0))}

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

The MMULT function is configured to return the same result as above, but the syntax used to create array2 is different:

TRANSPOSE(COLUMN(codes)^0))

The COLUMN function returns 6 numbers in a horizontal array:

COLUMN(data) // returns {3,4,5,6,7,8}

And these numbers are then raised to a power of zero with the exponent operator (^):

COLUMN(data)^0) // returns {1,1,1,1,1,1}

Raising any number to the power of zero (0) results in 1, so the result is the same 1 x 6 array filled only with 1s.

Finally, TRANSPOSE flips the array to a 1 x 6 array to a 6 x 1 array:

TRANSPOSE({1,1,1,1,1,1} // returns {1;1;1;1;1;1}

and the result is handed off to the MMULT function as array2, as before.

The first array in MMULT is created in the same way as in the original formula, with the same result. So the final result from MMULT is the same:

{0;0;0;0;1;0;0;0;0;0;0}

Substituting this array into the formula, we have a "standard" INDEX and MATCH formula:

=INDEX(group,MATCH(1,{0;0;0;0;1;0;0;0;0;0;0},0))

The MATCH function returns 5, the position of the single 1 in the array:

=INDEX(group,5) // returns "Epsilon"

And INDEX returns the 5th item in the named range group (B5:B15), "Epsilon", as the final result.