VLOOKUP vs XLOOKUP: Examples and Key Differences

VLOOKUP and XLOOKUP both find a value in a range and return related information, but XLOOKUP is generally more flexible and defaults to an exact match. For example, =VLOOKUP(E2,A2:B5,2,FALSE) and =XLOOKUP(E2,A2:A5,B2:B5,"Not found") can both look up the name in E2 and return a matching value from column B; the second formula uses separate lookup and return ranges and provides a result if no match exists.

How the two lookup functions differ

VLOOKUP has been a familiar Excel lookup function for many years. Its name means vertical lookup: it searches down the first column of a table and returns a value from another column in the same row. Its table-based design is straightforward, but it relies on a column number and has some constraints.

XLOOKUP is the newer function in Microsoft 365 and Excel 2021 or later. Instead of selecting a whole table and specifying a numbered return column, you provide one range to search and another range from which to return a result. This makes many formulas easier to read and less fragile when a worksheet changes.

Both functions are useful. VLOOKUP remains important when a workbook must work in older Excel versions, while XLOOKUP is usually simpler for new workbooks in a version that supports it. Neither function changes the source data: each searches for a match and returns a corresponding value.

Basic exact-match example

Suppose a small employee list has names in column A and annual salaries in column B. Cell E2 contains the name to search for, such as Priya Shah. With names in A2:A5, salaries in B2:B5, and the lookup name in E2, use this VLOOKUP formula:

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

If Priya Shah is in row 3 and B3 contains 65000, the result is 65000. The arguments mean: find E2 in the first column of A2:B5, return the value from column 2 of that table, and require an exact match. The final argument FALSE matters: without it, VLOOKUP uses approximate-match behavior by default.

With the same names in A2:A5, salaries in B2:B5, and the lookup name in E2, the equivalent XLOOKUP formula is:

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

This also returns 65000 for Priya Shah. The first range is where Excel searches, and the second range contains the values to return. XLOOKUP uses an exact match by default, so there is no need to add an exact-match setting for this ordinary lookup. The optional fourth argument supplies a readable result if the name is absent.

The examples assume that each name appears once. If a lookup value occurs more than once, both functions ordinarily return the first matching row they encounter from top to bottom. A lookup formula does not identify or resolve duplicate records; check the source data if duplicates should not exist.

What happens when no match exists?

When VLOOKUP cannot find an exact match, it returns the #N/A error. You can keep that error visible because it signals a missing or mismatched value, or wrap the lookup in IFNA to display a friendlier message. With names in A2:A5, salaries in B2:B5, and the search term in E2, this formula returns “Not found” if E2 is not in the list:

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

XLOOKUP has a built-in not-found argument. With the same cell layout, this formula returns the specified text when there is no match:

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

For example, if E2 contains Morgan Lee and that name is absent from A2:A5, the XLOOKUP formula displays Not found instead of #N/A. A custom message can make a report easier to read, but it can also hide a data problem. During cleanup or auditing, leaving the error visible may make missing records easier to spot.

Not every lookup error means a value is absent. Extra spaces, inconsistent spelling, numbers stored as text, and mismatched data types can prevent an expected match. Compare the lookup value with the source entries and standardize the data before assuming the formula is at fault.

Approximate matches and thresholds

Approximate lookups are useful when a value belongs to a band, such as a score mapped to a grade or a quantity mapped to a discount tier. For VLOOKUP with its final argument set to TRUE, the first column of the table must be sorted in ascending order. Excel finds the largest threshold that is less than or equal to the lookup value. If that column is unsorted, results may be incorrect.

Suppose D2:D6 contains the ascending score thresholds 0, 60, 70, 80, and 90, while E2:E6 contains the corresponding labels F, D, C, B, and A. If G2 contains 84, and the thresholds are in D2:D6 with labels in E2:E6, use:

=VLOOKUP(G2,D2:E6,2,TRUE)

The result is B because 80 is the largest threshold no greater than 84. The formula does not find the nearest threshold in either direction; it applies the approximate-match rule. A score below the smallest threshold may return #N/A, so the table should cover the values that users can enter.

XLOOKUP makes approximate matching explicit through its match_mode argument. With thresholds in D2:D6, labels in E2:E6, and the score in G2, this formula returns the exact match or, if there is no exact match, the next smaller item:

=XLOOKUP(G2,D2:D6,E2:E6,"No band",-1)

For 84, the result is B, matching the VLOOKUP example. XLOOKUP’s match_mode of -1 means exact match or next smaller value; 1 means exact match or next larger value. For approximate matching, keep the lookup values ordered in a way that fits the intended threshold logic and verify boundary cases, such as the smallest and largest possible inputs.

Looking left, changing columns, and searching backward

A key VLOOKUP limitation is that the lookup field must be in the leftmost column of the selected table, and the return field must be to its right. If a product table has SKUs in B2:B5 and product names in A2:A5, VLOOKUP cannot search column B and return from column A using that table arrangement. One workaround is to rearrange the columns or use another function.

XLOOKUP can search one range and return from a range on either side. With SKUs in B2:B5, product names in A2:A5, and the SKU to find in F2, use:

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

This searches the SKU column and returns the product name from the matching row, even though that return range is to the left. The ranges should have compatible dimensions so each lookup entry lines up with its corresponding return value.

VLOOKUP’s column-number argument can also become unreliable when someone inserts or rearranges columns within the table: the number may then point to a different field than intended. XLOOKUP names the lookup and return ranges separately, which often makes the formula easier to review after worksheet edits. However, a formula can still point to the wrong range if rows, columns, or source data are reorganized carelessly.

XLOOKUP can also search from the bottom upward. Its search_mode argument can be set to -1 to return the last matching entry rather than the first. This is handy when a log contains repeated IDs and the latest record is stored at the bottom, provided the row order really does represent recency. It is not a substitute for sorting or checking what the order means.

Compatibility and practical limitations

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Windows, Mac, and the web. It is not available as a native worksheet function in Excel 2019 or Excel 2016. If someone opens a workbook containing XLOOKUP in an unsupported version, the formula may show as an unsupported function rather than calculate correctly. Confirm the recipients’ Excel versions before replacing formulas in a shared workbook.

VLOOKUP is supported in older Excel releases, which is its main practical advantage over XLOOKUP. It can be a sensible choice for a workbook that must be opened by users with legacy software. For newer workbooks, XLOOKUP’s exact-match default, separate ranges, optional not-found result, and ability to look left make it easier to use in many situations.

Both functions perform a lookup based on the values supplied; they do not automatically ignore meaningful differences in text or repair inconsistent source data. Exact matching is not the same as a fuzzy search for nearly similar names. If the workbook needs to match variations such as misspellings or differently formatted identifiers, first determine a reliable data-cleaning or matching approach rather than expecting either formula to infer the intended record.

How to choose the right function

For a new Excel workbook, choose XLOOKUP when all users have a compatible version and you want a direct, maintainable formula. Choose VLOOKUP when compatibility with older Excel versions is a requirement, or when you are maintaining an existing workbook whose formulas already use it.

Use XLOOKUP for exact matches when you want a built-in not-found message or need to return a value from a column to the left.
Use VLOOKUP for a straightforward lookup in an older-compatible workbook, and specify FALSE for an exact match rather than relying on its default.
Use either function for threshold lookups only after confirming the matching rule and ordering of the lookup values.
Check for duplicate keys, blank cells, inconsistent formats, and missing records when a result seems surprising.

For a simple one-off task, either function can be perfectly adequate. For a workbook others will maintain, readable references and predictable matching rules matter more than choosing the newest function just because it is newer. Add a short note near a threshold table explaining what its breakpoints mean, and avoid suppressing errors when missing data needs investigation.

Frequently Asked Questions

Is XLOOKUP better than VLOOKUP?

For most new work in supported Excel versions, XLOOKUP is more flexible: it defaults to exact matching, can look left, and has a built-in not-found argument. VLOOKUP can be the better choice when the workbook must support Excel versions that do not include XLOOKUP.

Does XLOOKUP work in Excel 2019?

No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not a native function in Excel 2019. Use a compatible alternative, such as VLOOKUP for a right-side return, if the workbook must calculate in Excel 2019.

Why does VLOOKUP return the wrong result?

A common cause is omitting the final argument or using TRUE when an exact match was intended. VLOOKUP then uses approximate matching, which expects the first table column to be sorted ascending. For exact matching, use FALSE and check that the lookup value and source entries have consistent spelling and data types.

Can XLOOKUP return a value from a column on the left?

Yes. Set the lookup array to the column containing the search key and the return array to the column containing the answer. Unlike VLOOKUP, XLOOKUP does not require the return column to be to the right of the lookup column.

Do VLOOKUP and XLOOKUP return every duplicate match?

No. In their ordinary use, both return one result: the first matching entry from the top. XLOOKUP can be set to search from the bottom to return the last match, but neither function by itself returns a list of every matching row.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.