Don’t forget to share this post

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.

Atlassian Confluence 250x250 brand default
Claim Your Forever-Free Confluence Account!

Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

GET IT FOR FREE

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.
XLOOKUP in Google Sheets Examples Open Google Sheets
XLOOKUP in Google Sheets (+ Examples) - Open Google Sheets
  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.
XLOOKUP in Google Sheets Examples Search Key
XLOOKUP in Google Sheets (+ Examples) - Search Key
  1. 3. Type the XLOOKUP function and select the cell with the search key as the first parameter.
XLOOKUP in Google Sheets Examples XLOOKUP Function
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP Function
  1. 4. For the second parameter, select the column where you want to find the search key.
XLOOKUP in Google Sheets Examples Lookup Range
XLOOKUP in Google Sheets (+ Examples) - Lookup Range
  1. 5. For the third parameter, select the column from which you want the result. Skip the optional parameters to get the defaults.
XLOOKUP in Google Sheets Examples Result Range
XLOOKUP in Google Sheets (+ Examples) - Result Range
  1. 6. Close the parenthesis and press ‘Enter’. You have your result.
XLOOKUP in Google Sheets Examples XLOOKUP Result
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP 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.
XLOOKUP in Google Sheets Examples Search Key 2
XLOOKUP in Google Sheets (+ Examples) - Search Key
  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.
XLOOKUP in Google Sheets Examples Lookup Range 2
XLOOKUP in Google Sheets (+ Examples) - Lookup Range
  1. 3. For the third parameter, specify the row from which you want the result. Skip the optional parameters to get the defaults.
XLOOKUP in Google Sheets Examples Result Range 2
XLOOKUP in Google Sheets (+ Examples) - Result Range
  1. 4. Close the parenthesis and press ‘Enter’ to get the result.
XLOOKUP in Google Sheets Examples XLOOKUP Result 2
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP Result
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

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.
XLOOKUP in Google Sheets Examples Add Function
XLOOKUP in Google Sheets (+ Examples) - Add Function
  1. 2. For the first parameter, select the first search key, add an ampersand (&), and select the second search key.
XLOOKUP in Google Sheets Examples Search Keys 3
XLOOKUP in Google Sheets (+ Examples) - Search Keys
  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 (&).
XLOOKUP in Google Sheets Examples ARRAYFORMULA Lookup Range
XLOOKUP in Google Sheets (+ Examples) - ARRAYFORMULA Lookup Range
  1. 4. For the third parameter, select the column from which you want the result returned.
XLOOKUP in Google Sheets Examples Result Range 3
XLOOKUP in Google Sheets (+ Examples) - Result Range
  1. 5. Close the parenthesis and press ‘Enter’ to get the result.
XLOOKUP in Google Sheets Examples XLOOKUP Result 3
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP 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.
XLOOKUP in Google Sheets Examples Search Key 4
XLOOKUP in Google Sheets (+ Examples) - Search Key
  1. 2. For the second parameter, select the column where you want to find the search key.
XLOOKUP in Google Sheets Examples Lookup Range 4
XLOOKUP in Google Sheets (+ Examples) - Lookup Range
  1. 3. For the third parameter, add the ARRAYFORMULA function and select the columns from which you want the results.
XLOOKUP in Google Sheets Examples ARRAYFORMULA Result Range 2
XLOOKUP in Google Sheets (+ Examples) - ARRAYFORMULA Result Range
  1. 4. Close both parentheses and press ‘Enter’ to see the results.
XLOOKUP in Google Sheets Examples XLOOKUP Results
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP Results
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
XLOOKUP in Google Sheets Examples Partial Search Key
XLOOKUP in Google Sheets (+ Examples) - Partial Search Key
  1. 2. In an empty cell, type the XLOOKUP function.
XLOOKUP in Google Sheets Examples Add Function 2
XLOOKUP in Google Sheets (+ Examples) - Add 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 (&).
XLOOKUP in Google Sheets Examples Wildcard Partial Search Key
XLOOKUP in Google Sheets (+ Examples) - Wildcard + Partial Search Key
  1. 4. For the second parameter, select the column where you want to find the match.
XLOOKUP in Google Sheets Examples Lookup Range 5
XLOOKUP in Google Sheets (+ Examples) - Lookup Range
  1. 5. For the third parameter, select the column from which you want to get the result.
XLOOKUP in Google Sheets Examples Result Range 5
XLOOKUP in Google Sheets (+ Examples) - Result Range
  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 in Google Sheets Examples XLOOKUP Result 5
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP Result
VLOOKUP with IMPORTRANGE in Google Sheets
VLOOKUP with IMPORTRANGE in Google Sheets

You can use VLOOKUP and IMPORTRANGE to import data from another Google Spreadsheet. Here’s how it works step-by-step.

READ MORE

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.
XLOOKUP in Google Sheets Examples Named Ranges
XLOOKUP in Google Sheets (+ Examples) - Named Ranges
  1. 2. From the sidebar, click “Add a range”.
XLOOKUP in Google Sheets Examples Add a Range
XLOOKUP in Google Sheets (+ Examples) - Add a Range
  1. 3. Select the column you want to name and type in the name. Click “Done”.
XLOOKUP in Google Sheets Examples Name Range
XLOOKUP in Google Sheets (+ Examples) - Name Range
  1. 4. Repeat steps 2 and 3 until you’ve named all the necessary columns.
XLOOKUP in Google Sheets Examples Repeat as Needed
XLOOKUP in Google Sheets (+ Examples) - Repeat as Needed
  1. 5. In an empty cell, type the XLOOKUP function and add the search key.
XLOOKUP in Google Sheets Examples Add Function Search Key
XLOOKUP in Google Sheets (+ Examples) - Add Function + Search Key
  1. 6. For the second parameter, start typing the name of the column to search and select it from the dropdown.
XLOOKUP in Google Sheets Examples Select Named Lookup Range
XLOOKUP in Google Sheets (+ Examples) - Select Named Lookup Range
  1. 7. For the third parameter, start typing the name of the column to return and select it like before.
XLOOKUP in Google Sheets Examples Select Named Result Range
XLOOKUP in Google Sheets (+ Examples) - Select Named Result Range
  1. 8. Close the parenthesis and press ‘Enter’ to see the result.
XLOOKUP in Google Sheets Examples XLOOKUP Result 6
XLOOKUP in Google Sheets (+ Examples) - XLOOKUP Result

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

  • Centralized Knowledge: Access your team’s collective wisdom with ease.
  • Collaborative Workspace: Foster engagement with flexible project tools.
  • Seamless Communication: Connect your entire organization effortlessly.
  • Preserve Ideas: Capture insights without losing them in chats or notifications.
  • Comprehensive Platform: Manage all content in one organized location.
  • Open Teamwork: Empower employees to contribute, share, and grow.
  • Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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:

Atlassian Confluence 250x250 brand default
Claim Your Forever-Free Confluence Account!

Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

GET IT FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 31 2023, Updated Mar 22 2023