XLOOKUP in Google Sheets: Complete Guide (2026)

If you work with data in Google Sheets, you have probably wrestled with VLOOKUP limitations at some point. The good news: XLOOKUP in Google Sheets is here to replace it. Since its official rollout in 2023, XLOOKUP has quickly become the go-to lookup function for spreadsheet users who want cleaner formulas, fewer errors, and more flexibility. In this complete guide, you will learn exactly how XLOOKUP works, when to use it, and how it stacks up against VLOOKUP and INDEX MATCH.

Key Takeaways:XLOOKUP replaces both VLOOKUP and HLOOKUP with a single, more flexible functionIt searches any direction (left, right, up, down) without column index numbersThe built-in if_not_found parameter eliminates the need for IFERROR wrappersXLOOKUP supports exact match, wildcards, and approximate matching in one formulaFor large datasets beyond 100K rows, pair XLOOKUP with SmoothSheet for smooth imports

What Is XLOOKUP in Google Sheets?

XLOOKUP is a modern lookup function that Google added to Sheets in late 2023, following Microsoft Excel lead. It was designed as a direct replacement for both VLOOKUP and HLOOKUP, combining their capabilities into a single, more intuitive function.

Here is why XLOOKUP matters:

• No column index numbers. Unlike VLOOKUP, you specify the return range directly -- no more counting columns or breaking formulas when you insert a new column.
• Searches in any direction. VLOOKUP can only search left-to-right. XLOOKUP searches left, right, up, or down.
• Built-in error handling. The optional if_not_found parameter lets you set a default value when no match exists, so you do not need to wrap everything in IFERROR.
• Multiple match modes. Exact match, wildcard, next smaller, or next larger -- all from a single function.

If you are still using VLOOKUP or INDEX MATCH for every lookup, XLOOKUP is worth learning. It simplifies your formulas and makes your spreadsheets easier to maintain.

XLOOKUP Syntax and Parameters

Here is the full XLOOKUP syntax in Google Sheets:

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

Let us break down each parameter:

Required Parameters

Optional Parameters

Match Mode Values

Search Mode Values

The binary search modes (2 and -2) can significantly speed up lookups on large, sorted datasets. If your spreadsheet has tens of thousands of rows, this parameter is worth using. And if you are importing large CSV or Excel files into Google Sheets in the first place, SmoothSheet handles files that would otherwise crash your browser.

How to Use XLOOKUP Step by Step

Let us walk through the most common XLOOKUP scenarios using a sample product dataset:

Basic XLOOKUP (Simple Lookup)

Find the price of "Monitor":

=XLOOKUP("Monitor", A2:A6, C2:C6)

This searches column A for "Monitor" and returns the corresponding value from column C: $449.

Notice how you do not need a column index number. With VLOOKUP, you would write =VLOOKUP("Monitor", A2:C6, 3, FALSE) and have to know that Price is the 3rd column. With XLOOKUP, you point directly at the result column.

XLOOKUP with Default Value (No More IFERROR)

Search for a product that does not exist, with a friendly fallback:

=XLOOKUP("Tablet", A2:A6, C2:C6, "Not found")

Since "Tablet" is not in the list, this returns "Not found" instead of an ugly #N/A error.

Previously, you would need to write =IFERROR(VLOOKUP("Tablet", A2:C6, 3, FALSE), "Not found"). XLOOKUP handles this natively. For more on error handling strategies, check out our guide on mastering IFERROR in Google Sheets.

Reverse Lookup (Search Bottom to Top)

Find the last product in the "Electronics" category by searching from the bottom up:

=XLOOKUP("Electronics", B2:B6, A2:A6, "None", 0, -1)

With search_mode set to -1, XLOOKUP starts from the last row and works upward. This returns "Keyboard" -- the last Electronics product in the list.

This is something VLOOKUP simply cannot do. With VLOOKUP, you would always get the first match. To find the last match, you would need a complex array formula or helper columns.

XLOOKUP with Wildcards

Find any product that starts with "Note":

=XLOOKUP("Note*", A2:A6, C2:C6, "Not found", 2)

Setting match_mode to 2 enables wildcard matching. The * matches any number of characters, so "Note*" finds "Notebook" and returns $12.

Supported wildcards:

• * -- matches any sequence of characters
• ? -- matches any single character
• ~ -- escape character (use ~* to search for a literal asterisk)

XLOOKUP Returning Multiple Columns

Return both Category and Price for "Desk Chair" in one formula:

=XLOOKUP("Desk Chair", A2:A6, B2:C6)

By expanding the result_range to cover columns B through C, XLOOKUP returns multiple values that spill across adjacent cells: Furniture and $299.

This is a huge advantage over VLOOKUP, which can only return a single column. With multiple VLOOKUP results, you would typically need multiple formulas or helper functions.

XLOOKUP vs VLOOKUP vs INDEX MATCH

Here is how the three major lookup approaches compare:

When to Use Each

• Use XLOOKUP when you want the simplest, most readable formula. It is the best choice for most new spreadsheets. Its built-in error handling and multi-directional search make it the default recommendation.
• Use VLOOKUP if you are maintaining older spreadsheets and do not need to look left. It still works fine for simple right-to-left lookups. Read our guide on getting multiple VLOOKUP results if you are sticking with VLOOKUP.
• Use INDEX MATCH if you need maximum backward compatibility or are working with other spreadsheet platforms that do not support XLOOKUP yet.

Performance on Large Datasets

For small to medium datasets (under 10,000 rows), all three functions perform similarly. The differences become noticeable at scale:

• XLOOKUP with binary search (search_mode 2 or -2) is the fastest option on sorted data, using a logarithmic search algorithm.
• VLOOKUP with TRUE (sorted mode) also uses binary search and performs similarly.
• INDEX MATCH with sorted MATCH gives comparable speed.
• For unsorted data, all three perform linear scans and are roughly equivalent.

The real bottleneck with large datasets in Google Sheets is not usually the lookup function itself -- it is getting the data into Google Sheets in the first place. If you are working with CSV or Excel files over 100,000 rows, SmoothSheet imports them via server-side processing so your browser does not freeze. At $9/month, it is a lot cheaper than losing hours to crashed tabs.

Advanced XLOOKUP Techniques

Nested XLOOKUP (Two-Way Lookup)

A two-way lookup finds a value at the intersection of a specific row and column. You can nest XLOOKUP inside itself to achieve this.

Suppose you have a quarterly sales table with products in rows and quarters in columns. To find the sales for "Monitor" in "Q3":

=XLOOKUP("Monitor", A2:A6, XLOOKUP("Q3", B1:E1, B2:E6))

Here is how it works:

1. The inner XLOOKUP finds "Q3" in the header row (B1:E1) and returns the entire Q3 column (B2:E6).
2. The outer XLOOKUP then searches for "Monitor" in A2:A6 and returns the matching value from the Q3 column.

This replaces the classic INDEX(MATCH, MATCH) pattern with something much more readable.

XLOOKUP with Multiple Criteria

To look up a value based on two or more conditions, concatenate your criteria using the & operator:

=XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100, "Not found")

For example, if F2 contains "Electronics" and G2 contains "2026", this searches for a row where column A is "Electronics" AND column B is "2026".

Note: This creates an array operation. In Google Sheets, XLOOKUP handles this natively without needing Ctrl+Shift+Enter (as older Excel versions required for array formulas).

Dynamic Ranges with XLOOKUP

XLOOKUP works beautifully with other dynamic functions. Combine it with IMPORTRANGE to look up data across different spreadsheets:

=XLOOKUP(A2, IMPORTRANGE("spreadsheet_url", "Sheet1!A:A"), IMPORTRANGE("spreadsheet_url", "Sheet1!C:C"), "Not found")

You can also pair XLOOKUP with FILTER, SORT, or UNIQUE to create powerful dynamic reports. For instance, use XLOOKUP inside a FILTER to add lookup columns to filtered results:

=FILTER(A2:C100, XLOOKUP(A2:A100, D2:D50, E2:E50, "") <> "")

When your cross-spreadsheet data pulls involve large files, make sure Google Sheets can handle the volume. Use the Google Sheets Limits Calculator to check whether your combined data stays within the 10 million cell limit.

Frequently Asked Questions

Does Google Sheets have XLOOKUP?

Yes. Google added XLOOKUP to Google Sheets in late 2023. It is available to all users -- no special add-ons or beta programs required. If you do not see it, make sure your spreadsheet is set to a supported locale and try refreshing your browser.

Is XLOOKUP better than VLOOKUP?

For most use cases, yes. XLOOKUP is more flexible (searches any direction), more robust (built-in error handling), and easier to read (no column index numbers). VLOOKUP still works fine for simple lookups, but XLOOKUP is the better choice for new formulas. The only reason to stick with VLOOKUP is backward compatibility with spreadsheets shared with users on older platforms.

Can XLOOKUP look left?

Yes, and this is one of its biggest advantages over VLOOKUP. With XLOOKUP, the lookup_range and result_range are independent -- the result column can be to the left of the search column. For example, =XLOOKUP("$449", C2:C6, A2:A6) searches prices in column C and returns product names from column A.

What is the difference between XLOOKUP and INDEX MATCH?

Both can search in any direction and do not require column index numbers. The key differences: XLOOKUP is a single function (simpler syntax), has a built-in if_not_found parameter, and can return multiple columns natively. INDEX MATCH requires two nested functions but has been available in Google Sheets much longer. For a deep dive, see our INDEX MATCH guide.

Why is XLOOKUP not working in my Google Sheets?

Common reasons XLOOKUP might not work:

• Typo in function name. Make sure you are typing XLOOKUP, not XLOOKUP (with a trailing space) or X.LOOKUP.
• Mismatched range sizes. The lookup_range and result_range must have the same number of rows (or columns for horizontal lookups).
• Data type mismatch. If your search key is a number but the lookup range contains text-formatted numbers (or vice versa), XLOOKUP will not find a match. Use VALUE() or TEXT() to convert.
• Leading/trailing spaces. Hidden spaces in cells can prevent matches. Use TRIM() on both the search key and lookup range.
• Locale issues. Some locales use semicolons instead of commas as argument separators. Try =XLOOKUP("Monitor"; A2:A6; C2:C6) if commas do not work.

If you are importing data from CSV or Excel files and seeing lookup errors, the source data might have encoding issues or extra whitespace. SmoothSheet offers a CSV Joiner tool that can help you perform VLOOKUP-style joins across files before importing to Sheets, which can be faster than running lookups on huge datasets inside the spreadsheet itself.

Conclusion

XLOOKUP is the most significant formula upgrade Google Sheets has received in years. It combines the functionality of VLOOKUP, HLOOKUP, and much of INDEX MATCH into a single, cleaner function. Whether you are doing simple product lookups, multi-criteria searches, or two-way table queries, XLOOKUP handles it with less complexity and fewer errors.

Here is what to remember:

• Use XLOOKUP for all new lookup formulas -- it is simpler and more powerful than VLOOKUP.
• Take advantage of the if_not_found parameter to eliminate IFERROR wrappers.
• Use search_mode -1 to find the last matching value -- something VLOOKUP cannot do.
• For large sorted datasets, enable binary search (search_mode 2) for faster performance.

Working with large datasets that need lookup formulas? SmoothSheet imports massive CSV and Excel files into Google Sheets without browser crashes -- so you can focus on building your XLOOKUP formulas instead of fighting with frozen tabs. Try it for $9/month.