How to use XLOOKUP in Google Sheets (with examples)

According to Exploding Topics, over 900 million people use Google Sheets monthly. But how many use XLOOKUP in Google Sheets?

This tutorial will show you how to use it to pull data from the same spreadsheet or two different spreadsheets.

It also covers the one job where FILTER works better than XLOOKUP: returning every match.

In this guide:

• What XLOOKUP does and its six arguments
• How to use XLOOKUP, step by step
• XLOOKUP from another sheet
• XLOOKUP between two spreadsheets
• Examples: horizontal lookups, multiple criteria, wildcards, named ranges, default values, FILTER, and last or closest matches
• XLOOKUP vs VLOOKUP

What is XLOOKUP in Google Sheets?

In simple terms, XLOOKUP helps you find specific information in your Google Sheets. You tell XLOOKUP what to look for (like a student’s grade) and where to look (which column in your gradebook). Then, it quickly finds the matching information and returns it to you.

XLOOKUP is a more flexible alternative to older functions like VLOOKUP and HLOOKUP.

XLOOKUP syntax and arguments

XLOOKUP takes six arguments. Only the first three are required.

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

Excel names the same arguments lookup_value, lookup_array, return_array, and if_not_found, and some examples below use those names.

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. Open Google Sheets to the spreadsheet containing your table.

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

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

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

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

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

How to use XLOOKUP from another sheet

Let’s say you are a teacher and use a Google Sheet to track your students’ grades. We will name the spreadsheet “Students Exams.”

Now, the spreadsheet has two sheets:

• One called “Students” — It has the students’ names and ID numbers and
• One called “Test Scores” — It has the students’ ID numbers and scores.

— Sheet 1: Students

— Sheet 2: Test Scores

Keep in mind that these two sheets are in the same spreadsheet. We will show you how to use XLOOKUP in two separate spreadsheets later.

You want to see each student’s score next to their name in the “Students” sheet. Let’s choose John Doe. 

1. In the cell under “Test Score” for John Doe, use this XLOOKUP formula:

Let’s break this formula down:

• Lookup Value (A2): The ID number you’re looking for.
• Lookup Array (‘Test Scores’!A:A): This is the column in the “Test Scores” sheet where the XLOOKUP function should find the ID numbers.
• Return Array (‘Test Scores’!B:B): If it finds a match, it returns the corresponding value from the Return Array (the student’s score).
• If Not Found (“Not Found”): If it doesn’t find a match, it displays “Not Found.”

In simple terms, this is what the formula says:

2. Press “Enter” on your keyboard or click anywhere on your sheet. The score will appear.

Recommended reading: How to use the XLOOKUP function in Excel?

How do you use the XLOOKUP function in two separate Google Sheets?

Can you use the XLOOKUP function in two separate Google Sheets?

Yes. Wrap the lookup range and the result range in IMPORTRANGE so XLOOKUP reads them from the other file. The first time you connect the two files, Google Sheets asks you to allow access.

How does it work?

Use this formula:

Let’s return to our teacher’s example.

Imagine you are collaborating with another teacher on a project. You each have your own Google Sheet containing student data.

Your Google Sheet is called “Students” and contains student names and ID numbers.

On the other hand, your colleague’s sheet is called “Test Scores” and contains student ID numbers and their scores on a recent test.

You want to combine this information into your “Students” sheet to see each student’s score next to their name.

How do you do this?

1. Open your colleague’s sheet and copy the ID from the URL. It starts after ‘/d/’ and ends before the next “/.”

2. Replace the two “sheet-id” in the formula below with the ID you just copied.

3. Copy the name of the sheet from your colleague’s spreadsheet.

4. Replace the two “Sheet1” in the formula with your colleague’s sheet name, “Test Scores.” This is what your final formula will look like:

5. In the cell under “Test Score” for Jane Smith, use the XLOOKUP formula above.

6. Press “Enter” on your keyboard or click anywhere on your sheet. The score will appear.

7. If you want to get Jane Smith’s score in row 3, change "=XLOOKUP(A2" to "=XLOOKUP(A3."

Old formula with (A2:

New formula with (A3:

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 several columns at once, search using wildcards, and use named ranges. The last examples show a default value for missing matches, FILTER for returning every match, and how to find the last or the closest match.

Horizontal XLOOKUP (instead of HLOOKUP)

XLOOKUP also searches across a row, so you can use it instead of HLOOKUP. Follow the steps below to perform a horizontal lookup.

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.

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.

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

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

XLOOKUP with multiple criteria

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

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

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

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 (&).

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

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

Return several columns with one XLOOKUP

To return several values from the matching row, such as the email, product, and amount, you can use the ARRAYFORMULA function. XLOOKUP still returns only the first match. To get every matching row, use FILTER, as shown further down.

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

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

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

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. Type the partial search key into a cell for later reference.

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

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 (&).

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

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

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. Open the spreadsheet with your table and go to Data > Named ranges.

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

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

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

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

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

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

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

Show a default value when there’s no match

You can use XLOOKUP in Google Sheets to find missing values in a dataset by comparing two lists (e.g., a list of existing products and a list of new products).

Let’s look at an example.

You’re a sales manager tracking employee performance in one Google Sheet. You have:

• Sheet 1 (Employee Data): Contains employee names and ID numbers.

• Sheet 2: Contains employee ID numbers and their sales figures.

• Sheet 3: Contains the consolidated information. However, some employees do not have sales data, as shown below.

Keep in mind that these three sheets are in the same spreadsheet.

1. Open Sheet 3 (“Consolidated” in our example).
2. In cell C2 of Consolidated, enter the following XLOOKUP formula:

This formula will:

1. Look for the Employee ID in B2 within the range of Employee IDs in Sheet 2 (Sheet2!A:A).
2. If found, it will return the corresponding Sales Figure from Sheet 2 (Sheet2!B:B).
3. If not found, it will return the value 0, indicating no sales.

Keep in mind that you need to edit the formula before using it. For example, my sheet is called “Sales,” not “Sheet2” and the “102” ID is in row B3, not B2. So, my new formula will be:

Use it in the cell and press Enter on your keyboard.

Return every match with FILTER

People often ask us if XLOOKUP can return multiple results in Google Sheets.

The best way to answer this question is through an example.

Let’s say you manage a small bookstore and keep track of your inventory in a Google Sheet. Your sheet has the following columns:

• Book Title
• Author
• ISBN
• Quantity in Stock
• Price

You want to find all the books a specific author wrote and their ISBNs and quantities in stock.

1. Identify your lookup value. In this example, I will go for an author’s name since it’s the value I want to find: J.K. Rowling.
2. Identify the lookup array, the column containing the author names: Author.
3. Identify the return arrays, the columns you want to return data from:
   • ISBN
   • Quantity in Stock
4. Create the XLOOKUP formula:

Remember that you need to edit this formula to match your scenario.

5. Open your sheet and use your formula in an empty cell.

6. Press “Enter.” As you can see, XLOOKUP only returns the first matching book, although the formula checks every row.

Can XLOOKUP return every matching row in Google Sheets?

Not really. XLOOKUP typically returns a single match or the first match it finds. Unfortunately, it does not natively support returning multiple matches as FILTER does. So, in its standard form, XLOOKUP is unsuitable for pulling various rows.

Did you notice that I mentioned FILTER? How does it work?

7. Change your formula to:

8. Press “Enter.” And voilà, FILTER pulled data from multiple rows.

Find the last match or the closest value

The last two arguments change how XLOOKUP searches. These examples use the sales table from the step-by-step section, where column F holds the amount and column G the sales agent.

To get the amount of the last sale in the list for Agent2, set search_mode to -1 so XLOOKUP searches from the bottom up:

=XLOOKUP("Agent2", G:G, F:F, "Not found", 0, -1)

To find the closest value instead of an exact one, set match_mode to -1 (next smaller) or 1 (next larger). Say K2:K5 holds sales thresholds and L2:L5 the commission rate for each. This formula returns the rate for the highest threshold that isn’t above the amount in F2:

=XLOOKUP(F2, K2:K5, L2:L5, , -1)

Neither formula needs sorted data. Only the binary search modes, 2 and -2, do.

XLOOKUP vs VLOOKUP: what’s the difference?

VLOOKUP searches vertically, from left to right, within a table (hence the “V”); the lookup value must be in the first column. XLOOKUP, however, is more flexible. It can search vertically and horizontally; you don’t need to specify a column index.

VLOOKUP can also only return values to the right of the lookup column, while XLOOKUP can return values from either side.

Here is the difference in terms of syntax:

VLOOKUP

XLOOKUP

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 pull data from another sheet or another file, search with multiple criteria or wildcards, and return several columns at once. When a lookup comes up empty, missing_value gives you a default, and when you need every match, FILTER does what XLOOKUP can’t.

If you repeat the same lookups across several files every week, Sheetgo, an AI-powered data automation platform, can connect those spreadsheets and keep the combined data updated on a schedule, without IMPORTRANGE formulas to maintain.