Arrow left
Back to product guides
Google Sheets Formulas

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

  1. Type “=XLOOKUP” or go to “Insert”“Function” “Lookup” “XLOOKUP”.
  2. Enter  “search key”, “lookup_range”, and “result_range”.
  3. Press “Enter”.
How to insert the XLOOKUP formula from the menu bar  in Google Sheets

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.

How to use the XLOOKUP for a vertical search in Google Sheets with an example

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. 

How to use the XLOOKUP for a horizontal search in Google Sheets with an example

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.

How the XLOOKUP returns multiple values in Google Sheets

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

How to use the XLOOKUP for a vertical search in Google Sheets with another example

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: 

  1. Run a search with an exact match mode as default;
  2. 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;
  3. Work as an alternative to the HLOOKUP function;
  4. Return more than one value;
  5. Search an item from the bottom to the top in the target range;
  6. Conduct binary search;
  7. Define a value returned when an error happens;
  8. Approximate search returning the closest bigger value; and 
  9. 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).

Analyze your live financial data in a snap

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.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711