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"

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 available in Excel 365 only.
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.