XLOOKUP - Google Sheets: Explained
In this article, you will learn how to use the XLOOKUP in Google Sheets, which was released by Google in late August 2022.
The XLOOKUP is more flexible and easier to use than the VLOOKUP and HLOOKUP when you want to search a data set for a specific item.
How to use the XLOOKUP in Google Sheets
- Type “=XLOOKUP” or go to “Insert” → “Function” → “Lookup” → “XLOOKUP”.
- Enter “search key”, “lookup_range”, and “result_range”.
- Press “Enter”.
The generic formula is as follows;
Search_key: This is the keyword the formula uses for its search in the specified range defined by “Lookup_range”
Lookup_range: This is where the formula looks for “search_key”. This range should be a single column or row.
Result_range: This is the range in which the formula looks for a value corresponding to “search_key”. The number of rows (when “lookup_range” is a single column”) or columns (when “lookup_range” is a single row”) in “result_range” should be the same as that of “lookup_range”.
Missing_value (Optional): A value shows up in a cell when the formula can’t find any match. If you don’t enter any value here, the function returns “#N/A”.
Match_mode (Optional): This determined how the function looks for “search_key” in “lookup_range”. You can enter 0, 1, -1, or 2, or leave the part blank.
0: The formula looks for an exact match.
1: This tries to find an exact match or the closest value bigger than “search_key”.
-1: This searches for an exact match or the nearest value lower than “search_key”.
2: This is for a wildcard match.
Blank: If search_mode is unspecified, it works the same as “0”.
Search_mode (Optional): You can type 1, -1, 2, or -2, or keep the section blank
1: This is to find a value from the first entry to the last one in the selected “result_range”.
-1: This works opposite to 1. The formula searches from the last one to the first entry.
2: With 2, the function searches the range with binary search, assuming the range is arranged in descending order.
-2: The tries to find a value through the range with binary search, assuming the selected field is sorted in ascending order.
Blank: If search_mode is unspecified, it works the same as “1”.
If you are interested in how these “match_mode” and “search_mode” work, please read a section of this article about the XMATCH.
See how the XLOOKUP is used practically in the examples below. Assume that you are a finance manager and try to pull specific data out of raw data, “Raw data - Revenue by Client”. You want to find revenue data in FY2019 for the Sunflower company.
Imagine you know which column has the search keyword and where the target data sits but you don’t know which row to look for. In this example, the XLOOKUP can work as a tool for a vertical search.
Look at the formula in the picture above. As the last three arguments can be blank, we filled in the first three arguments, “search key”, “lookup_range”, and “result_range”. With these inputs, the formula analyzes “lookup_range”, the area surrounded by purple lines for the “search_key” called “Sunflower”.
After that, the function goes to “result_range”, the field surrounded by light blue lines and finds the specific data matching the same row that has “Sunflower”.This is the third row in the selected range that returns the result we’re looking for - 208.
See the second example below. With the same sample data, assume that you need to find revenue data in FY2020 for Iris company. This time, you know in which row the search keyword and target data sit, but you don’t know which column they are in. You can make the XLOOKUP work for a horizontal search in this case.
The third example shows that the XLOOKUP can return multiple values depending on how you enter the argument of “result_range”. As you can see, we selected the entire data for “result_range” in this example, which allows the function to give us the entire row corresponding to the “search_key” of “Rose”, specifically the first row in the selected range.
This is an example of a vertical search but you can apply this feature to a horizontal search, too.
The fourth example shows how flexible the XLOOKUP is. You can’t do this type of search with the VLOOKUP function. When you use the VLOOKUP formula, the column containing a search keyword must be in the leftmost position in the selected range.
You can leverage this flexibility for the horizontal search as well. It means the row containing specific data you want to pull out can be upper than the row for a search keyword. However, the rule doesn’t apply to the XLOOKUP function. Also, this picture shows how you can enter values for the last three arguments in the formula, such as “N/A”, “0” and “1”.
What is the equivalent of XLOOKUP in Google Sheets?
The major alternative methods of the XLOOKUP function are INDEX/MATCH, VLOOKUP, HLOOKUP and Filter functions. See these articles if you want to learn them.
For INDEX/MATCH: How To Create Dynamic Dashboards with Index/Match
For VLOOKUP: How to Use VLOOKUP in Google Sheets
For HLOOKUP: How to Use HLOOKUP in Google Sheets
What is the difference between VLOOKUP and XLOOKUP?
The major differences between the two formulas are summarized below.
The XLOOKUP can:
- Run a search with an exact match mode as default;
- Return a value even when a target column (from which you want to pull out a specific; item) is to the left of a column for a keyword search;
- Work as an alternative to the HLOOKUP function;
- Return more than one value;
- Search an item from the bottom to the top in the target range;
- Conduct binary search;
- Define a value returned when an error happens;
- Approximate search returning the closest bigger value; and
- Work as it is when you insert new columns in a raw data table (with the assumption that there is no duplication with existing items).
.png){kind=small}
