The Ultimate Guide to the XLOOKUP Google Sheets Function

The XLOOKUP Google Sheets function is a recent addition and helps Google catch up to Microsofts superior search functions. Check out our comprehensive guide below to learn everything you need to know.

What Is XLOOKUP?

The XLOOKUP function is one of the newer functions introduced relatively recently in Excel in 2019. It works similarly to lookup functions like HLOOKUP, VLOOKUP, and INDEX with MATCH. However, XLOOKUP is arguably easier to use and more robust.

For people using Microsoft Excel, XLOOKUP has quickly become the preferred method for users performing a search.

Google Sheets also recently introduced the XLOOKUP function.

Here is the syntax for the XLOOKUP formula:

=XLOOKUP(lookup-value, lookup-array, return-array, [if-not-found], [match-mode], [search-mode])

Here are the parameters used in the formula:

• lookup-value: This is the value you are looking for.
• lookup-array: This parameter is the array or cell range you wish to search.
• return-array: This is the range or the array to return the value from.
• if-not-found: This optional parameter specifies what should be output when a match is not found when the search ends. If left blank, this will return a #N/A error.
• match-mode: This optional parameter controls how matches will be found.
• search-mode: This is also an optional parameter that controls how the formula will perform the search.

Does Google Sheets Have XLOOKUP?

Originally, Google Sheets didn’t have an equivalent XLOOKUP function. However, a recent update finally introduced the XLOOKUP function in Google Sheets. This function works the same as it does in Microsoft Excel.

What Is the XLOOKUP Function Used For?

The XLOOKUP in Google Sheets performs advanced lookups and data retrieval from a table or range.

It is designed to provide more flexibility and simplicity than other lookup functions like VLOOKUP, HLOOKUP, and INDEX/MATCH. XLOOKUP can handle both vertical and horizontal lookups and supports a wide range of lookup scenarios.

It can be used to perform the following kinds of lookups:

1. Basic lookups
2. Vertical and horizontal lookups
3. Exact matches and approximate matches
4. Lookups specifying return values if not found
5. Lookups with multiple criteria
6. Lookups across sheets

How To Use XLOOKUP Function in Google Sheets

We’ll use the following example spreadsheet for this guide to the XLOOKUP function in Google Sheets.

 

You can follow the link for our EXAMPLE SPREADSHEET to make a copy.

How To Find Exact Match Using XLOOKUP

Suppose you have the example sheet below and you would like to look up one of the Employee’s sales. We can find Brenda’s Sales numbers for February using the XLOOKUP function.

 

Here’s how to use the XLOOKUP function in Google Sheets:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.

 

3. Select the cell with the lookup value. In our case, that is cell F2. You can also enter the lookup value manually in quotation marks.

 

4. Select the column to search for the lookup value.

 

5. Add a comma and select the column to return the value from.

 

6. Click “Enter.”

 

The formula we used for this example is:

=XLOOKUP(F2,A2:A7,C2:C7)

This formula returns an exact match for the value in the February sales column that matches Brenda in the name column.

How To Find Approximate Match Using XLOOKUP

You can also use the XLOOKUP function to find an approximate match. For example, in our spreadsheet, we wanted to find the person who made approximately 140 sales in February.

 

Here’s how to use XLOOKUP in Google Sheets for approximate matches:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.

 

3. Select the cell with the lookup value. In our case, that is cell F2. You can also enter the lookup value manually in quotation marks.

 

4. Select the column to search for the lookup value.

 

5. Add a comma and select the column to return the value from.

 

6. Add two commas, then add 1.

 

7. Click “Enter.”

 

The formula we’ve used for this example is:

 =XLOOKUP(F3,B2:B8,A2:A8,,1)

This formula looks for the value 140 in the second column and finds an approximate match. It then returns the corresponding in the first column.

XLOOKUP If Not Found

If the XLOOKUP formula can’t find the lookup value, it usually returns the N/A error like the one below:

 

This may affect the overall look of your data. Fortunately, the formula can return a specific text if the value is not found. Here’s how:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.

 

3. Select the cell with the lookup value. You can also enter the lookup value manually in quotation marks.

 

4. Select the column to search for the lookup value.

 

5. Add a comma and select the column to return the value from.

 

6. Add another comma and type the words “Not found” in quotation marks.

 

7. Click “Enter.”

 

The formula we’ve used for this example is:

 =XLOOKUP(D2,A2:A7,B2:B7,"Not found")

If the lookup value is not in the range, the formula will return the words “Not found” instead of the N/A error.

XLOOKUP with Multiple Values

If you want to return multiple values from one match, you can use the XLOOKUP with a broader range for the return array.

Let’s look at our example below:

 

We can get all of Brenda’s sales using the XLOOKUP. Here’s how:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.

 

3. Select the cell with the lookup value. In our case, that is cell F2. You can also enter the lookup value manually in quotation marks.

 

4. Select the column to search for the lookup value.

5. Add a comma and select the range from which you want to return the multiple values.

 

6. Click “Enter.”

 

The formula we’ve used for this example is:

 =XLOOKUP(F2,A2:A8,B2:D8)

This formula returns multiple values from the selected three columns that match with Brenda.

How To Use XLOOKUP with Multiple Criteria

Doing a Google Sheets XLOOKUP multiple criteria is not as difficult as it may seem. What you’ll need to do is use XLOOKUP with multiple criteria.

Let’s look at our example below. We want to find the person who got approximately 180 sales in January and 150 in February:

 

Here’s how to use Google Sheets XLOOKUP multiple criteria:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.

 

3. Select the first lookup value and add the ampersand sign (&), then select the second lookup value.

 

4. Add a comma and enter the ARRAY FORMULA.

 

5. In the brackets, select the lookup range for the first lookup value.

 

6. Add the ampersand sign (&), then select the second range for the second lookup value.

 

7. Close the brackets and add a comma.
8. Select the column you want to return the value from. In our case, this is the name column.

 

9. Add two commas and 1 for an approximate match.

 

10. Click “Enter.”

The formula we’ve used in this case is:

=XLOOKUP(F2&G2,ARRAYFORMULA(B2:B7&C2:C7),A2:A7,,1)

This XLOOKUP multiple criterion Google Sheets formula returns the value corresponding to the approximate values that meet the two conditions.

How To Use XLOOKUP Across Multiple Sheets

If you have data in multiple sheets in Google Sheets, you can still perform a lookup using the XLOOKUP.

In our example, we can have each month’s sales in different sheets.

 

We would have to do each sheet one by one to work with multiple sheets.

Here’s how to perform an XLOOKUP for Google Sheets for 2 sheets:

1. Enter the equals sign (=) in the appropriate cell.
2. Type the XLOOKUP function.
3. Select the cell with the lookup value.

 

4. Go to the first sheet and select the range to look into.

 

5. Add a comma and select the range from which you want to return the value.

 

6. Click “Enter.”

 

Do the same for each sheet until you fill your table.

 

The formula we’ve used for this example is:

 XLOOKUP(A2,'Jan sales'!A2:A7,'Jan sales'!B2:B7)

The formula performs the XLOOKUP across 2 sheets and returns a match from the second sheet.

Array Arguments to XLOOKUP are of Different Size

You can’t use the XLOOKUP function in Google Sheets with arguments of different sizes. The formula usually returns the N/A error like below:

 

The only way around this is to make the arguments the same size. You can do this by finding the smaller argument and selecting more blank cells.

In our example, the first argument has a smaller range.

 

You can select more cells to match the second argument.

Benefits of the XLOOKUP Function

The following are the benefits of the XLOOKUP function:

1. It can perform a left-to-right lookup as well as a right-to-left lookup.
2. LOOKUP could potentially provide array-like features, enabling you to perform operations across data ranges more efficiently.
3. XLOOKUP supports handling multiple results. This simplifies scenarios where you must retrieve multiple values matching your criteria.
4. The default for the XLOOKUP formula is usually an exact match, so you don’t need any more parameters to get the exact match.
5. The XLOOKUP doesn’t only return one value but can also return multiple values or even entire rows or columns.

Alternatives for XLOOKUP in Google Sheets

Here’s how to do an XLOOKUP equivalent search in Google Sheets.

The FILTER Function

Using FILTER in Sheets is the best way to get similar results to XLOOKUP in Sheets. Let’s look at the syntax before we demonstrate it with an example. We changed it to represent something similar to Google Sheet XLOOKUP. It is:

=FILTER(return-array, lookup-array=lookup-value)

We are going to need three parameters here. These are:

• return-array: This parameter defines the range or array to return.
• lookup-array: This parameter specifies the array or range you wish to search.
• lookup-value: This parameter will define the value of the cell address of the value you’re searching for.

Here is an example of using the FILTER formula as a Google Sheets XLOOKUP equivalent:

 

The exact formula we used here is:

=FILTER(B2:B7, A2:A7=D2)

The first parameter defines the cell range we want the value. The second and third parameters define the cell range you want to search from and the value you wish to search.

The FILTER function is extremely powerful as it can return an array with more than 1 matching value. It can also filter depending on conditional criteria and add a condition on more than 1 variable.

The INDEX and MATCH Function

Another XLOOKUP alternative in Google Sheets is a combination of the INDEX and the MATCH functions together. Let’s take a look at the syntax we will be using for this purpose:

=INDEX(return-range, MATCH(lookup-value, lookup-range, 0))

We are nesting the MATCH formula inside the INDEX function in this example.

Here are the parameters used in these formulas:

• return-range: This parameter is the cell range containing the value you want.
• lookup-value: This parameter defines the value you’re looking for.
• lookup-range: This parameter defines the range containing the value in the lookup-value parameter.

Let’s look at what the function looks like in action. We are using the same data from above.

 

The exact formula we used here is:

=INDEX(B2:B7,MATCH(D2,A2:A7,0))

The first parameter is the range we wish to return the value from. The second parameter is the MATCH function, and this formula has three parameters. The first argument is the value we want to look for in the range defined in the second parameter in the MATCH formula.

HLOOKUP and VLOOKUP

If you’re looking for how to do XLOOKUP in Google Sheets, these functions work almost identically, except you need to know if you’re searching vertically or horizontally across the table. I.e., H for horizontal and V for vertical.

We have complete guides for each of these functions. You can check them out here:

• VLOOKUP Multiple Criteria in Google Sheets
• VLOOKUP From Another Sheet
• How To Use The Google Sheets HLOOKUP Function

Frequently Asked Questions

Does Google Sheets Have XLOOKUP?

If you’re wondering, does XLOOKUP work in Google Sheets? You’ll be glad to know that Google Sheets now has the XLOOKUP function. There are alternative functions you can use. These include FILTER, HLOOKUP, VLOOKUP, and INDEX (MATCH).

What Is the Equivalent of XLOOKUP Function in Google Sheets?

Although there are several functions you can use as an alternative to XLOOKUP, mostly FILTER and INDEX(MATCH) will get the job done.

Can You Use XLOOKUP Instead of VLOOKUP?

You can use XLOOKUP instead of VLOOKUP in Excel, and the only difference is that XLOOKUP will work no matter the direction of the lookup, and the returned values will reside in the array or cell range. Unfortunately, XLOOKUP does not work in Google Sheets.

Wrapping Up

In the past, you may have run into trouble importing ready-made spreadsheets from Excel containing XLOOKUP formulas, but now you can easily use the XLOOKUP Google Sheets function.

Related:

• How To Use LOOKUP in Google Sheets (Easy Tutorial)
• How To Get the Last Value in a Column in Google Sheets (Lookup Formula)
• The 3 Google Sheets Wildcards and How to Use Them
• Google Sheets Notifications: How to Turn On, Edit & Remove
• How To Use IFERROR in Google Sheets [with Examples]