Table of Contents
XLOOKUP is a powerful function in Google Sheets that enables you to search for a value in one range and return a result from another range. It replaces the older VLOOKUP and HLOOKUP functions and has a number of advantages, such as being able to search both vertically and horizontally, making it easier and faster to find what you are looking for. To use XLOOKUP, you specify the search key, the range in which to search, and the range from which the result should come. You can also specify whether the search should be exact or approximate, and whether it should be sorted or unsorted. An example of how to use XLOOKUP is provided below.
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 .
The following tutorials explain how to perform other common operations in Google Sheets: