How to use XLOOKUP in Google Sheets? (+ formulas and 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.

We will also discuss the best and easiest alternative to XLOOKUP.

Keep reading!

What is XLOOKUP?

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.

How to use XLOOKUP in Google Sheets?

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, you must ensure both sheets are open in your browser and that you reference the other sheet correctly in your XLOOKUP formula.

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:

Examples of XLOOKUP in Action

Example 1: Missing Value

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 in the cell and press Enter on your keyboard.

Example 2: Return Multiple Results

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 one value, although our formula explicitly asks for all rows to be checked.

7. Nevertheless, there is still a little way to retrieve all the data. Click the first returned data.

And drag it down. More data will appear, though it’s often repeated.

Can you use XLOOKUP to return multiple results 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?

8. Change your formula to:

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

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.

Here is the difference in terms of syntax:

VLOOKUP

XLOOKUP

Use XLOOKUP in Google Sheets

We just saw how to use XLOOKUP in Google Sheets through examples. We also shared raw formulas and explained how to use them.