This tutorial will teach you how to use the XMATCH function to search for a value in multiple columns in Google Sheets. You will learn how to code the formula step-by-step so that you can understand it and modify it as needed.

The primary purpose of match functions, such as MATCH or XMATCH, in Google Sheets is to find the position of an item in a column or row. It will return a number relative to the rows in the array, not rows in the spreadsheet. We can then use that number for reference or in other functions like INDEX, FILTER, QUERY, etc., to do additional tasks.

The XMATCH function is for searching a one-dimensional array. It requires at least two arguments to work: search_key and lookup_range. The lookup_range must be a one-dimensional array.

Syntax of the XMATCH Function in Google Sheets:

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

When you want to XMATCH multiple columns, you may be required to drag the formula across. However, this will not meet your requirements when you want to use it with other functions such as INDEX or FILTER.

So, how do we expand the XMATCH lookup range to all columns in a table?

We can use one of the LAMBDA functions, precisely the MAP function, with some other functions, to do this.

XMATCH for Multiple Columns in Google Sheets (No Dragging)

Assume you have a calendar view template in Google Sheets and want to search for a particular date on it to find its relative position.

The calendar view range is B3:H16, and the date to search is in cell J6.

Usually, to XMATCH multiple columns, you would use the following formula in cell K6 and drag it across until you get a value other than #N/A:

=XMATCH($J$6,B3:B16)

The formula will return 9 in the fourth column. This is because, in the formula, the search_key reference is absolute and the lookup_range reference is relative. So, the column reference in the lookup_range increases when you drag it across.

Here is how to use the MAP lambda with XMATCH to search down multiple columns without dragging the formula across:

=SORTN(MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col)))))

Important: Remove the SORTN() wrapper to get matching values from all columns, if any. In this case, the results would be {#N/A; #N/A; #N/A; 9; #N/A; #N/A; #N/A} because there is only one matching value.

This formula applies the XMATCH function to each column in the range B3:H16, using the value in cell J6 as the search key. The MAP function then returns an array of results, which is passed to the SORTN function to return the first value in the array, other than #N/A.

You can use this formula with INDEX in two ways:

1. To return a value from any column in the same row.
2. To return a value from the same column from any row.

We will see these two additional tips after the XMATCH for multiple-column formula explanation.

Formula Explanation

We have used the INDEX, COLUMNS, SEQUENCE, SORTN, and MAP functions to use XMATCH for multiple columns in Google Sheets. Here is a breakdown of their roles:

1. Base Formula

The base formula is =XMATCH($J$6,B3:H16). However, this formula will not work because it violates the XMATCH syntax, which does not allow multiple columns to be used as the lookup range.

To fix this, we can use the INDEX function to return the first column from B3:H16, which will give XMATCH a single column (one-dimensional array) to search. This formula is shown below:

=XMATCH($J$6,INDEX(B3:H16,0,1))  // Formula #1

The INDEX function takes three arguments: the reference, row, and column. In the formula above, the column argument is set to 1, which means that the INDEX function will return the first column from the reference (lookup range).

Let’s keep this formula aside for the time being.

2. Virtual Array for Mapping

We can use the COLUMNS function to find the number of columns in the lookup range. If we wrap the COLUMNS function with the SEQUENCE function, we will get an array of numbers as follows: {1; 2; 3; 4; 5; 6; 7}.

=SEQUENCE(COLUMNS(B3:H16))  // Formula #2

Yep! We have 7 columns in the range.

This array can be used to represent the columns in the lookup range, specifically the column argument of the INDEX formula in Formula #1.

Just replacing 1 with Formula #2 is not enough. We need to map each value in Formula #2 using the MAP function. This is equivalent to using XMATCH for multiple columns. We will learn it in the next step. Keep this formula aside too.

3. MAP Function for Using XMATCH in All Columns

Here is the generic formula that we will follow to XMATCH multiple columns in Google Sheets:

Generic Formula:

MAP(formula#2, LAMBDA(col,formula_expression))

Where:

• formula#2 is the sequence of column numbers.
• col is the name of formula#2.
• formula_expression is the base formula with the column argument in INDEX replaced with the name col.

Here is the formula coded based on the generic formula above:

=MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col))))  // Formula #3

This formula will return #N/A in columns that have no match. We can wrap this formula with SORTN to remove those errors and return only the matching value. This is our final XMATCH multiple-column formula.

XMATCH Multiple Columns: Return a Value from the Matching Row

I think it will be simple to explain how to use XMATCH in multiple columns and return a value from the matching row with the following generic formula:

=INDEX(
   reference,
   xmatch_multiple_columns_formula
)

Where:

• reference is the column from which you want to return the value.
• xmatch_multiple_columns_formula is the formula that returns the relative position of a search key after matching it in multiple columns.

Let’s apply this to a real-life scenario.

Suppose you have the following table in the range A1:D, where A1:A contains country names and B1:D contains wheat production in million tonnes over the last three years. You want to XMATCH a particular wheat production quantity in column B to D and return the country name from column A.

If the search key is in cell G2, the following formula will do that:

=INDEX(
   A1:A,
   SORTN(MAP(SEQUENCE(COLUMNS(B1:D)),LAMBDA(col,XMATCH(G2,INDEX(B1:D,0,col)))))
)

I know that some of you may know how to edit the XMATCH function in this combo to return an approximate match. However, I do not recommend doing so when using XMATCH for multiple columns and returning a value.

Creating a drop-down from a range B2:D in cell G2 is a good way to ensure that the search key is always an exact match. This will prevent errors from occurring in the formula.

XMATCH Multiple Columns: Return a Value from the Matching Column

You may need to return a value from the matching row in different ways. If you want to return the header, use the following generic formula:

=LET(
   key,
   xmatch_multiple_columns_formula,
   INDEX(B1:D,1,XMATCH(1,key,1))
)

If you want to return the value just below the matching value, use the following generic formula:

=LET(
   key,
   xmatch_multiple_columns_formula,
   INDEX(B1:D,sortn(KEY)+1,XMATCH(1,key,1))
)

To get the value just above the matching value, replace +1 with -1.

In both formulas, replace xmatch_multiple_columns_formula with the formula without the SORTN() wrapper.

Conclusion

You can use XLOOKUP to perform an approximate match of the search key. However, if you edit the formula, it is important to be aware of the implications.

For example, you can use an approximate match to return the relative position of the search key in all columns. To do this, you need to remove the SORTN() wrapper from the formula. However, it is important to avoid using an approximate match when you want to return a value after matching, as this could lead to unexpected results.

Related:

1. Hlookup to Search Entire Table and Find the Header in Google Sheets (HLOOKUP).
2. Search Across Columns and Return the Header in Google Sheets (QUERY and BYCOL).
3. Lookup and Retrieve the Column Header in Google Sheets (Searches First Column).
4. XMATCH Visible Rows in Google Sheets.
5. XMATCH First or Last Non-Blank Cell in Google Sheets.