How to Use XLOOKUP in Google Sheets (With Example)


The XLOOKUP function in Google Sheets can be used to look up a value in a range and return some corresponding value.

This function uses the following basic syntax:

=XLOOKUP(search_key, lookup_range, result_range)

where:

  • search_key: The value to search for.
  • lookup_range: The range to search in.
  • result_range: The range that contains the value to return.

The following example shows how to use this function in practice.

Example: How to Use XLOOKUP in Google Sheets

Suppose we have the following dataset in Google Sheets that contains information about various basketball players:

We can use the following XLOOKUP function in Google Sheets to look up the value in E2 in the range A2:A11 and return the corresponding value in the range C2:C11:

=XLOOKUP(E2, A2:A11, C2:C11)

The following screenshot shows how to use this formula in practice:

XLOOKUP function in Google Sheets example

The XLOOKUP function looks up the value “Rockets” in column A and returns the corresponding value in the “Points” column.

Note that you can also use a fourth argument in the XLOOKUP function to specify what value should be returned if no match is found.

Note: The default is to return #N/A if no match is found.

For example, we can use the following formula to return “Not Found” if no match is found:

=XLOOKUP(E2, A2:A11, C2:C11, "Not Found")

The following screenshot shows how to use this formula in practice:

Google Sheets XLOOKUP function with missing value

Since “Wizards” doesn’t exist in column A, a value of “Not Found” is returned.

Note: You can find the complete documentation for the XLOOKUP function here.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets: How to Extract Top N Values from Range
Google Sheets: How to Filter for Cells that Contain Text
Google Sheets: How to Extract Numbers from String

Leave a Reply

Your email address will not be published. Required fields are marked *