XLOOKUP vs VLOOKUP: Key Differences and When to Use Each

XLOOKUP and VLOOKUP both find a value in a table and return related information, but they differ in flexibility, defaults, and compatibility.

A small example shows why the newer function often takes less effort—and when VLOOKUP is still the safer choice.

How do XLOOKUP and VLOOKUP work?

VLOOKUP searches vertically in the first column of a table and returns a value from another column in the same row.

Its name means “vertical lookup.” XLOOKUP searches a specified lookup range and returns a value from a separate return range, so the search and result columns do not need to be part of one table range.

Suppose cells A2:A4 contain product codes, and B2:B4 contain product names:

• A2: P-101; B2: Notebook
• A3: P-102; B3: Pen set
• A4: P-103; B4: Desk lamp

To return the name for code P-102, XLOOKUP is:

=XLOOKUP("P-102",A2:A4,B2:B4,"Not found")

The expected result is Pen set.

The equivalent VLOOKUP formula is:

=VLOOKUP("P-102",A2:B4,2,FALSE)

Here, 2 tells VLOOKUP to return the second column of A2:B4, and FALSE requests an exact match.

What are the main differences?

Why is XLOOKUP usually easier to maintain?

It can return values from either side

VLOOKUP requires the lookup column to be the leftmost column in its table range.

If codes are in B2:B4 and names are in A2:A4, VLOOKUP cannot return names directly from that arrangement.

XLOOKUP can:

=XLOOKUP("P-102",B2:B4,A2:A4,"Not found")

This matters when a worksheet layout is fixed or when moving columns would disrupt other formulas and reports.

It does not rely on a column number

VLOOKUP’s third argument is a column index.

In =VLOOKUP("P-102",A2:C4,3,FALSE), the formula returns the third column of the selected range.

Inserting a column within that range can make the index point to a different field than intended.

XLOOKUP names the return range directly:

=XLOOKUP("P-102",A2:A4,C2:C4,"Not found")

That structure makes the formula easier to read and reduces dependence on the relative position of columns.

It uses exact matching by default

With XLOOKUP, an omitted match-mode argument means exact matching.

VLOOKUP defaults to approximate matching when its last argument is omitted or TRUE.

For an exact VLOOKUP, use FALSE or 0 explicitly.

Approximate VLOOKUP can produce an unexpected result if the first column is not sorted as required.

How do you handle missing matches?

XLOOKUP accepts an optional fourth argument for the value to return when it cannot find a match:

=XLOOKUP(E2,A2:A100,C2:C100,"No matching code")

If E2 is not present in A2:A100, the result is No matching code.

With VLOOKUP, wrap the formula in IFERROR:

=IFERROR(VLOOKUP(E2,A2:C100,3,FALSE),"No matching code")

IFERROR also replaces other errors, so it can hide problems beyond a missing lookup value.

If you need to distinguish a missing match from another error, use a more specific error check where supported.

Can XLOOKUP search in reverse or return several columns?

Yes.

XLOOKUP’s optional search-mode argument can search from the last item toward the first.

For example, to return the most recent matching status when dates or records are listed in order:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found",0,-1)

The final -1 sets the search direction to last-to-first.

This is useful when a lookup value appears more than once and the last occurrence is the one you need.

XLOOKUP can also return more than one adjacent column when the return range includes those columns.

For example, if A contains codes and B:C contain a name and category, =XLOOKUP(E2,A2:A100,B2:C100,"Not found") can return both fields into neighboring cells in versions that support dynamic arrays.

The formula may spill into cells to the right; those cells need to be empty.

Which Excel versions support XLOOKUP?

XLOOKUP is available in Microsoft 365 and newer perpetual Excel releases, including Excel 2021 and Excel 2024.

It is not available in Excel 2019 or Excel 2016.

A workbook containing XLOOKUP may display #NAME? in an unsupported version.

VLOOKUP is supported by older Excel versions and remains useful when a workbook must work across mixed or legacy installations.

Google Sheets also supports XLOOKUP and VLOOKUP, though function availability and behavior should be checked in the specific spreadsheet app and version used by collaborators.

What edge cases should you check?

• Duplicate keys: XLOOKUP normally returns the first matching value; use reverse search when the last match is intended.
  
  VLOOKUP also returns the first match it encounters.
• Numbers stored as text: A numeric code and a text-formatted version of the same digits may not match as expected.
  
  Standardize the data type in the lookup and source ranges.
• Extra spaces: Leading or trailing spaces can make text values appear identical while failing an exact match.
  
  Clean the source data or use a helper column with TRIM.
• Wildcards: XLOOKUP’s default match mode is exact.
  
  Its wildcard mode is available through the match-mode argument; VLOOKUP with FALSE also supports wildcard characters in text lookups.
• Approximate matches: XLOOKUP can use match modes for exact-or-next-smaller or exact-or-next-larger results.
  
  Confirm the ordering and intended rule before relying on approximate lookup behavior.
• Spilled results: A multi-column XLOOKUP result can return #SPILL! if cells in the output area are occupied.

When should you use each function?

Choose XLOOKUP for new workbooks when all users’ spreadsheet versions support it.

Its separate ranges, exact-match default, not-found argument, and ability to search in either direction make many formulas clearer and easier to adapt.

Choose VLOOKUP when you need compatibility with older Excel versions, are maintaining an existing workbook built around it, or know that the lookup value is in the leftmost column and the result lies to its right.

Whichever function you use, specify the match behavior deliberately and test cases for missing values, duplicates, and inconsistent data types.