XLOOKUP vs VLOOKUP in Google Sheets: Which to Use?

In Google Sheets, XLOOKUP is usually the more flexible choice: it can search in either direction, uses exact matches by default, and does not rely on a numbered return-column index. VLOOKUP remains useful for familiar, simple lookups, but it searches only the first column of its selected range and needs an explicit FALSE argument when you want an exact match.

What XLOOKUP and VLOOKUP do

Both functions find a value in a range and return a related value from another range. For example, a product list might have product codes in one column and prices in the next. A formula can search for a code and return its price without requiring you to locate the row manually.

The main difference is how you tell each function where to search and what to return. VLOOKUP takes one rectangular table range, then a number identifying the result column. XLOOKUP takes a lookup range and a result range separately. That makes XLOOKUP easier to adapt if your columns move or if the return value is to the left of the search column.

Google Sheets supports both functions. XLOOKUP is not an Excel-only function, and choosing it in Sheets does not require switching to Microsoft Excel. If you share a spreadsheet with someone who exports it to an older spreadsheet program, however, check that program’s function support before relying on XLOOKUP.

How the formulas work

Suppose product codes are in A2:A5, prices are in B2:B5, and the code to find is in D2. The rows contain P-101 at $12.00, P-102 at $18.50, P-103 at $24.50, and P-104 at $31.00. With those cells in place, this formula uses XLOOKUP:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found")

If D2 contains P-103, the result is 24.50. The arguments are the value to search for, the range to search, the aligned range to return a value from, and an optional message for a missing match. XLOOKUP looks for an exact match by default, so you do not need to add a match setting for this common use.

With the same cell layout, the VLOOKUP version is:

=VLOOKUP(D2,A2:B5,2,FALSE)

This also returns 24.50 for P-103. Here, A2:B5 is the table, 2 tells Sheets to return the second column of that table, and FALSE requests an exact match. That last argument matters: if you leave it out, VLOOKUP uses approximate matching by default, which can return an unexpected result when the first column is not sorted appropriately.

Key differences at a glance

Feature	XLOOKUP	VLOOKUP
Search direction	Can return a value from either side of the lookup range	Returns values to the right of the first column in the selected table
Exact-match default	Yes	No; specify FALSE
Return-column selection	Choose a result range directly	Enter a numeric column index
Search orientation	Can search a row or a column	V means vertical; searches the first column
Missing match	Can include a custom missing-value result	Usually wrap with IFNA or IFERROR for a custom message

The practical difference is not that one function can look up data and the other cannot. Both can handle many everyday exact-match tasks. XLOOKUP simply describes the search and return ranges more directly, while VLOOKUP’s table-and-index structure makes it sensitive to how the table is arranged.

Why XLOOKUP is often easier to maintain

VLOOKUP’s column index is counted from the left edge of the selected table, not from the whole sheet. If the table is A2:C100 and the answer is in column C, the index is 3. Insert a new column inside that selected table and a formula that still says 3 may point to a different field. This is a common reason a lookup keeps calculating but starts returning the wrong information.

XLOOKUP names the range to search and the range to return separately. If the code is in B2:B100 and the product name is in A2:A100, XLOOKUP can search the codes and return names from the column to their left. With the lookup code in D2, this formula returns the name or the message if there is no match:

=XLOOKUP(D2,B2:B100,A2:A100,"Not found")

VLOOKUP cannot return a value to the left of the first column in its table range. You would need to rearrange the source columns, use a different formula, or create a helper range. This makes XLOOKUP a sensible default for new formulas when the spreadsheet will be edited over time.

When VLOOKUP is still a good fit

VLOOKUP is not obsolete just because XLOOKUP is available. If a table is stable, the search key is in its first column, and the returned field is to the right, a VLOOKUP formula can be easy to read, especially in a workbook already built around it. Many spreadsheet users recognize the function, and keeping existing formulas consistent can be more useful than changing them without a reason.

Use VLOOKUP when a simple, exact lookup already works and its table layout is unlikely to change.
Use it in a shared workflow where another tool or user expects that familiar function, after confirming compatibility.
Use FALSE for exact matching unless you deliberately want approximate matching and understand the sorting requirement.

There is usually no need to replace every VLOOKUP in a working sheet. Consider changing one when the formula is hard to maintain, needs to return a column on the left, or has become fragile because its index number no longer matches the table.

Match options, duplicates, and search order

XLOOKUP offers optional controls beyond the default exact match. Its match mode can request an exact match, an exact match or the next smaller value, an exact match or the next larger value, or a wildcard match. Its search mode can search from the first item toward the last or in reverse. These options can help with tasks such as finding the latest matching entry in a list, but they should be chosen deliberately.

When a key appears more than once, a normal top-to-bottom lookup returns the first matching row. XLOOKUP can search from the bottom upward when you choose its reverse search mode, which is useful when the last occurrence is the one you want. VLOOKUP does not offer the same search-direction argument; it returns the first match it encounters.

Approximate matches deserve extra care. VLOOKUP with TRUE, or with its final argument omitted, is not a substitute for an exact lookup: it assumes an appropriately sorted first column. XLOOKUP’s next-smaller and next-larger options make the intended behavior more explicit, but the data and match mode still need to suit the task. For a product code, employee ID, or other unique identifier, exact matching is normally the right choice.

Common lookup problems in Google Sheets

A lookup that returns #N/A may simply mean that Sheets cannot find an exact match. Check that the search value is present and that both sides use the same kind of value. For example, a number stored as text may not behave like a numeric value. Extra spaces, inconsistent punctuation, or a misspelled code can also prevent a match.

Confirm that the lookup and return ranges cover corresponding rows or columns.
Check whether the lookup key is stored as text in one place and as a number in another.
Look for leading or trailing spaces and inconsistent capitalization or punctuation.
For VLOOKUP, verify that the lookup column is the first column in the table range and that the column index is correct.
For an approximate VLOOKUP, confirm that the first column is sorted as required; otherwise, use FALSE for an exact match.

A custom not-found value can make an XLOOKUP result easier to interpret, but it can also hide the fact that a key is missing. During setup, displaying a clear message such as “Not found” is often more informative than returning a blank. If you use a blank result in a finished report, make sure missing data will not be mistaken for a valid empty value.

For VLOOKUP, a common way to show a friendly message instead of an error is to use IFNA. With the same layout as the earlier example—codes in A2:A5, prices in B2:B5, and the search code in D2—use:

=IFNA(VLOOKUP(D2,A2:B5,2,FALSE),"Not found")

This returns the price for a match and “Not found” if VLOOKUP produces a not-available error. Use error handling thoughtfully: a formula that replaces every error with a blank can conceal a broken range or other problem that should be fixed.

Choosing between them

For a new Google Sheets lookup, XLOOKUP is generally the clearer starting point. It makes an exact match the default, avoids the return-column counting step, and supports looking to either side of the search range. These advantages are most noticeable in sheets that change as new fields are inserted or reorganized.

Choose VLOOKUP when its straightforward table structure fits the data, or when maintaining an established workbook is the priority. Whichever function you use, decide whether you need an exact or approximate match, verify the ranges, and test the formula with a key that is present as well as one that is absent. A formula that returns the expected answer for one row is not proof that every row is aligned correctly.

Frequently Asked Questions

Does Google Sheets have XLOOKUP?
Yes. XLOOKUP is available in Google Sheets and can search a row or a column, then return a corresponding value from an aligned result range.

Is XLOOKUP better than VLOOKUP in Google Sheets?
For most new formulas, XLOOKUP is more flexible and has a safer exact-match default. VLOOKUP can still be a good choice for simple, stable tables and existing workbooks.

Can VLOOKUP look to the left in Google Sheets?
No. VLOOKUP searches the first column of its selected table and returns a value from a column to its right. XLOOKUP can search one range and return a corresponding value from a range to its left.

Why does VLOOKUP return the wrong result?
Check whether its final argument is set to FALSE for an exact match. If omitted, VLOOKUP defaults to approximate matching, which can produce unexpected results when the lookup column is not sorted as required. Also verify the table range and return-column index.

Does XLOOKUP return the last matching value?
By default, XLOOKUP searches from the beginning and returns the first match. Its search-mode option can reverse the search direction so that it finds the last occurrence instead.

Google Sheets versus Excel

The comparisons and formulas here are for Google Sheets in a web browser. Excel also supports XLOOKUP in modern versions, including Microsoft 365 and Excel 2021 or later, but function availability differs in older Excel releases. A spreadsheet shared between Sheets and Excel may also be affected by differences in supported features or how files are converted, so confirm the formulas in the application that will be used to edit the file.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.