Google Sheets Function: XLOOKUP

The XLOOKUP function searches for a value in a range of cells and then returns the value of a cell located in a different range of cells, at the same position.

This function is generally used to search for a value in one column of a table and return another value located on the same row of the table.

The XLOOKUP function is more functional than the VLOOKUP function, which is limited to searching for the value in the first column of the table, and simpler to use than the INDEX + MATCH combination.

Usage:

=XLOOKUP(search_key, lookup_range, result_range)

or

=XLOOKUP(search_key, lookup_range, result_range, if_not_found, match_mode, search_mode)

Example of use

The objective here is to be able to enter the name of the city in cell E2 and then automatically obtain the desired information in the blue cells.

To start, the formula in cell G2 should return the file number:

Enter into the XLOOKUP function:

• search_key: the value to search for in the lookup_range (here, the name of the city)
• lookup_range: the range of cells containing the data useful for the search (here, the column of cities)
• result_range: the range of cells containing the result data (here, the column of file numbers)

The formula here is:

=XLOOKUP(E2,B2:B11,A2:A11)

The file number corresponding to the searched city is then correctly returned by the function.

To then display the number of points, simply copy the formula and change the result_range:

Optional Arguments

In the previous example, only the 3 mandatory arguments were specified, but there are 3 more optional ones:

• if_not_found: the value to return if no match is found
• match_mode: the method for finding a match:
  • 0: exact match (default option)
  • 1: exact match or the next higher value to search_key
  • -1: exact match or the next lower value to search_key
  • 2: match with wildcard character
• search_mode: the search mode:
  • 1: search from the first entry to the last (default option)
  • -1: search from the last entry to the first
  • 2: binary search in the range assuming the range is sorted in ascending order
  • -2: binary search in the range assuming the range is sorted in descending order

The most useful is certainly if_not_found which allows to specify the value to return if no result is found and thus avoid the #N/A error:

Wildcard Characters

By setting match_mode to 2, you can use wildcard characters in the search, for example "*" to replace no, one, or multiple characters:

=XLOOKUP(E2,B2:B11,A2:A11,"",2)

Or to avoid entering this character each time in the search field, add it directly in the formula:

=XLOOKUP(E2&"*",B2:B11,A2:A11,,2)

Returning the Entire Row

It is possible to return multiple cells at once with a single XLOOKUP function.

To return the entire row, enter the entire table in result_range:

=XLOOKUP(E2,B2:B11,A2:C11)