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.


=XLOOKUP(search_key, lookup_range, result_range)


=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:

google sheets xlookup

Enter into the XLOOKUP function:

The formula here is:

google sheets xlookup function

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:

google sheets xlookup function

Optional Arguments

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

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:

google sheets xlookup function no result

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:

google sheets xlookup function wildcard characters

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

google sheets xlookup function wildcard character

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:

google sheets xlookup function multiple results
If needed, you can copy the Google Sheets document (or view the document) with these examples.