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