Exceljet

Quick, clean, and to the point

XLOOKUP match any column

Excel formula: XLOOKUP match any column
Summary 

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.

Explanation 

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, 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 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.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.