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:

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:

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 .

Additional Resources