Do you constantly have to look up information in your Google Sheets tables? If you currently use VLOOKUP or HLOOKUP, you may wish there was something a little more flexible when doing complex lookups. You can combine functions like INDEX and MATCH for more complex lookups, but you can do the same much more easily with XLOOKUP.

In this guide, you will learn all about XLOOKUP and its function syntax. You have step-by-step instructions on how to find an exact match for a single search key, both vertically and horizontally. Additionally, you have examples of other types of lookups, where you will learn to use XLOOKUP to match multiple search keys, return multiple values related to your search key, and use it with named ranges.

What is XLOOKUP in Google Sheets?

The XLOOKUP function in Google Sheets allows you to perform complex lookups, horizontally and vertically, without having to resort to combining the INDEX and MATCH functions. You can search for a value in any row or column and return the corresponding value from the parallel row or column you specify.

XLOOKUP Function & Syntax

The XLOOKUP function has six parameters, but only the first three are required.

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

• search_key: the value you want to search for. It can be text, a number, or a cell reference.
• lookup_range: the range in which you want to search for the value. It must be a single column or row.
• result_range: the range from which you want to return the result.
• missing_value*: optional. The value that you want returned if no match is found. By default, the function returns “#N/A”.
• match_mode*: optional. Specifies the type of match to be made. By default, the function looks for an exact match (0).
  • 0 returns an exact match.
  • 1 returns an exact match or the next value that is greater than the search_key.
  • -1 returns an exact match or the next value that is less than the search_key.
  • 2 returns a wildcard match.
• search_mode*: optional. Specifies the way in which the search should be performed. By default, it searches from the first entry to the last (1).
  • Use “1” to search from the first entry to the last.
  • Use “-1” to search from the last entry to the first.
  • Use “2” to search through the range with a binary search. If you choose this option, the range has to be sorted in ascending order.
  • Use “-2” to search through the range with a binary search. If you choose this option, the range has to be sorted in descending order.

XLOOKUP vs. VLOOKUP: What’s the Difference?

While VLOOKUP can only search vertically, XLOOKUP searches horizontally and vertically. Additionally, VLOOKUP can only return values to the right of the lookup range, but XLOOKUP can return values from either side. This makes XLOOKUP a more flexible function, capable of performing complex lookups and returning multiple results.

How to Use XLOOKUP in Google Sheets: Step-by-Step

In this section, you will learn how to use XLOOKUP vertically to find an exact match using a single search key.

1. 1. Open Google Sheets to the spreadsheet containing your table.

1. 2. Instead of typing the search key directly into the function, you can type it into a cell and later use the cell reference.

1. 3. Type the XLOOKUP function and select the cell with the search key as the first parameter.

1. 4. For the second parameter, select the column where you want to find the search key.

1. 5. For the third parameter, select the column from which you want the result. Skip the optional parameters to get the defaults.

1. 6. Close the parenthesis and press ‘Enter’. You have your result.

XLOOKUP Examples in Google Sheets

Now that you know how to use XLOOKUP to find an exact match for a single value, it’s time to learn about different ways of using it. In the examples below, you will learn to use XLOOKUP to perform horizontal lookups, search using multiple criteria, return multiple values, search using wildcards, and search using named ranges.

Horizontal XLOOKUP

Follow the steps below to perform a horizontal lookup using XLOOKUP.

1. 1. In an empty cell, type the XLOOKUP formula and add the search key as the first parameter. In this case, the search key will be the column header you want to match.

1. 2. For the second parameter, select the row where you want to find the search key. In this case, the headers are in row 1.

1. 3. For the third parameter, specify the row from which you want the result. Skip the optional parameters to get the defaults.

1. 4. Close the parenthesis and press ‘Enter’ to get the result.

XLOOKUP with Multiple Search Values

To search for multiple values using XLOOKUP, you can combine it with ARRAYFORMULA.

1. 1. In an empty cell, type the XLOOKUP function.

1. 2. For the first parameter, select the first search key, add an ampersand (&), and select the second search key.

1. 3. For the second parameter, type the ARRAYFORMULA function and select the columns where you want to find the search keys, joined by an ampersand (&).

1. 4. For the third parameter, select the column from which you want the result returned.

1. 5. Close the parenthesis and press ‘Enter’ to get the result.

XLOOKUP with Multiple Search Results

To return multiple results associated with your search key, you can use the ARRAYFORMULA function.

1. 1. In an empty cell, type the XLOOKUP function and add the search key as the first parameter.

1. 2. For the second parameter, select the column where you want to find the search key.

1. 3. For the third parameter, add the ARRAYFORMULA function and select the columns from which you want the results.

1. 4. Close both parentheses and press ‘Enter’ to see the results.

XLOOKUP with Wildcards

If you only have or remember bits and pieces of your search key, you can use wildcard characters to find a match.

• * matches zero or more characters.
• ? matches exactly one character.
• ~ nullifies wildcard characters so you can search for them.

1. 1. Type the partial search key into a cell for later reference.

1. 2. In an empty cell, type the XLOOKUP function.

1. 3. Add the wildcard and partial search key, as shown below. The wildcard character should be between quotation marks and joined to the partial search key using an ampersand (&).

1. 4. For the second parameter, select the column where you want to find the match.

1. 5. For the third parameter, select the column from which you want to get the result.

1. 6. Since you’re searching using a wildcard, you’ll need to specify that the fifth parameter is “2”. However, you don’t need to specify the fourth and sixth parameters if you want the default values.

XLOOKUP with Named Ranges

Finally, let’s see how you can use named ranges in XLOOKUP to make the process even easier.

1. 1. Open the spreadsheet with your table and go to Data > Named ranges.

1. 2. From the sidebar, click “Add a range”.

1. 3. Select the column you want to name and type in the name. Click “Done”.

1. 4. Repeat steps 2 and 3 until you’ve named all the necessary columns.

1. 5. In an empty cell, type the XLOOKUP function and add the search key.

1. 6. For the second parameter, start typing the name of the column to search and select it from the dropdown.

1. 7. For the third parameter, start typing the name of the column to return and select it like before.

1. 8. Close the parenthesis and press ‘Enter’ to see the result.

Conclusion

As you have seen, the Google Sheets’ XLOOKUP function is more powerful than VLOOKUP and HLOOKUP, and easier to use than alternatives like INDEX & MATCH. You’ve reviewed the function and its syntax and now know how to use XLOOKUP to do vertical and horizontal lookups for a single exact match. You also know how to use XLOOKUP to search using multiple criteria or search keys, return multiple values related to your search key, and use named ranges to simplify the process further.

To learn more about VLOOKUP, check out these guides on:

• How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)
• VLOOKUP with IMPORTRANGE in Google Sheets