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.
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 XLOOP is used practically in the examples below. Assume that you are a finance manager and try to pull specific data out of a 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 in 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 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 seach 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”.
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
The major differences between the two formulas are summarized below.
The XLOOKUP can:
Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇