VLOOKUP vs XLOOKUP: What's the Difference (and Which Should You Use)?

VLOOKUP and XLOOKUP both solve the same basic problem — search for a value in one column and pull back a corresponding value from another column in the same row — and for a huge share of everyday spreadsheet work, either one gets the job done. But XLOOKUP is a genuinely newer, more capable function built specifically to fix a handful of long-standing VLOOKUP limitations that have quietly caused real errors in spreadsheets for years, and knowing exactly what changed (not just "XLOOKUP is newer") is what actually helps you pick the right one and avoid the mistakes each is prone to.

What VLOOKUP actually does

VLOOKUP takes four arguments: the value you're searching for, the range to search in, which column (counted from the left edge of that range) to pull the result from, and whether you want an exact or approximate match. Written out: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The function always searches the leftmost column of the range you give it, and always returns a value from a column to the right of that — it has no way to look leftward, which is the root of its biggest practical limitation, covered below.

What XLOOKUP actually does

XLOOKUP separates the lookup range and the return range into two independent arguments instead of bundling them into one table with a column offset: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Because the range you search and the range you return from are specified separately, XLOOKUP has no inherent "must be to the right" restriction — the return_array can be a column anywhere relative to the lookup_array, including to its left.

The five real differences that matter

• Direction — VLOOKUP only searches the leftmost column of its range and only returns values from columns to the right of it. XLOOKUP's lookup and return ranges are independent, so it can just as easily pull a value from a column to the left of the one it searched.
• Column-insertion safety — VLOOKUP's col_index_num is a hardcoded number ("pull from the 3rd column"), so inserting or deleting a column anywhere inside the range silently shifts which column that number now points to, breaking the formula without an error. XLOOKUP's return_array is a direct cell-range reference, which Excel automatically adjusts when columns are inserted or deleted, so the formula keeps pointing at the right data.
• Default match behavior — VLOOKUP's fourth argument defaults to an approximate match (TRUE) if you leave it out, which silently returns the closest value below your search term instead of erroring when there's no exact match — a genuinely common source of confidently-wrong results. XLOOKUP defaults to an exact match, which is what most everyday lookups actually want.
• Built-in error handling — VLOOKUP returns a bare #N/A error when nothing matches, which normally means wrapping the whole formula in IFERROR(...) to show something more useful. XLOOKUP has an if_not_found argument built directly into the function, so you can specify the fallback value inline without a second wrapping formula.
• Search direction and method — XLOOKUP can optionally search from the last item to the first (useful for finding the most recent match in a chronologically ordered list) and can use a binary search on sorted data for better performance on very large ranges. VLOOKUP has neither option.

Side-by-side comparison

• VLOOKUP — searches only left-to-right within one combined range; breaks silently on inserted/deleted columns; approximate match by default; needs IFERROR for custom error handling; available in every version of Excel and Google Sheets currently in wide use.
• XLOOKUP — searches in either direction using two independent ranges; unaffected by inserted/deleted columns; exact match by default; has a built-in if_not_found argument; only available in Excel 365/2021 and newer, and Google Sheets since 2022 — not in Excel 2019 or earlier, and not in most older LibreOffice/OpenOffice builds.

A worked example — VLOOKUP

Say column A has employee IDs, column B has names, and column C has departments, and you want to look up the department for employee ID "E104". The formula is =VLOOKUP("E104", A:C, 3, FALSE) — search for "E104" in the leftmost column of range A:C, and return the value from the 3rd column of that range (column C), using an exact match. If someone inserts a new column between B and C later, that "3" now silently points at the wrong column, and the formula keeps returning a value — just the wrong one, with no error to flag it.

The same lookup with XLOOKUP

The same task in XLOOKUP: =XLOOKUP("E104", A:A, C:C, "Not found"). Here the lookup range (A:A) and the return range (C:C) are specified directly as their own column references, so inserting a column anywhere else in the sheet doesn't shift what C:C points to — it's still literally the Department column. The fourth argument, "Not found", is what gets returned instead of a bare #N/A error if the employee ID doesn't exist, with no extra IFERROR wrapper needed.

Left lookups — where VLOOKUP genuinely can't help

This is XLOOKUP's clearest practical win. If your employee IDs are in column C and the names you want to look up by are in column D, but you need to return something from column A (to the left of both), VLOOKUP simply cannot do it directly — its search column always has to be the leftmost column of the range, with results pulled from columns after it. The traditional VLOOKUP-era workaround was nesting it inside CHOOSE(), or reordering/duplicating columns just to get the search column to the left of the target — both real, if awkward, patches. XLOOKUP removes the need for either, since =XLOOKUP(lookup_value, D:D, A:A) works exactly the same whether the return column sits to the left or right of the lookup column.

Does XLOOKUP replace INDEX/MATCH too?

For most everyday purposes, yes. INDEX/MATCH became the standard workaround for VLOOKUP's leftward-lookup limitation long before XLOOKUP existed, combining MATCH (find the row position of a value) with INDEX (return a value from that row in a different column) to search in either direction. XLOOKUP was built specifically to replace that combination with a single, more readable function that does the same job — for the common case of a single-value lookup, XLOOKUP is generally the more direct choice now. INDEX/MATCH still has a few advanced use cases XLOOKUP doesn't cleanly cover (some two-dimensional lookup patterns, for instance), so it hasn't disappeared entirely from advanced spreadsheet work, but for a typical one-value-in, one-value-out lookup, reaching for XLOOKUP over INDEX/MATCH is now the more common recommendation.

Availability — check before you build a formula around XLOOKUP

XLOOKUP requires Excel 365 or Excel 2021 (or newer); it is not available in Excel 2019, Excel 2016, or earlier standalone (non-subscription) versions, and it's absent from most versions of LibreOffice Calc and OpenOffice Calc as well. Google Sheets gained XLOOKUP support in 2022, so any Sheets file created or edited since then can generally use it. If you're building a spreadsheet that will be opened by other people, or by a workplace still on an older, non-subscription Excel license, worth explicitly checking what version they're on before leaning on XLOOKUP — a file with XLOOKUP formulas opened in an unsupported version shows a #NAME? error instead of the expected result, which can be a confusing surprise for whoever opens it next.

Common mistakes with each

• Leaving VLOOKUP's fourth argument blank or set to TRUE by habit, silently getting approximate-match results for a lookup that actually needed an exact match.
• Hardcoding VLOOKUP's col_index_num as a fixed number, then having it silently break when a column gets inserted or deleted anywhere inside the lookup range.
• Building a spreadsheet around XLOOKUP without checking whether everyone who needs to open it actually has a version that supports it.
• Wrapping VLOOKUP in nested IFERROR/IFNA formulas that duplicate what XLOOKUP's built-in if_not_found argument already does in one step.
• Forgetting that VLOOKUP's search column must be the leftmost column of the range you give it — trying to search a middle column and return from the first column simply doesn't work without a workaround.

What about HLOOKUP?

HLOOKUP is VLOOKUP's horizontal counterpart — instead of searching down the leftmost column of a range, it searches across the top row and returns a value from a row below it, taking a row_index_num instead of a col_index_num. It shares the exact same limitations as VLOOKUP (top-row-only search direction, a hardcoded row index that breaks silently when rows are inserted, approximate match by default), just rotated 90 degrees. It's used far less often in practice simply because most real-world tables are organized with records running down rows rather than across columns, but the underlying logic — and the same fixes (explicit exact-match argument, care around inserted rows) — carries over directly. XLOOKUP handles the horizontal case too, using the same function with row-oriented ranges instead of column ones, so there's no separate "HLOOKUP equivalent" to learn on top of it.

Looking up with more than one condition

Neither VLOOKUP nor XLOOKUP natively takes two separate search criteria as two separate arguments — but both have a workable pattern for it. The traditional VLOOKUP approach is adding a helper column that concatenates the two values you want to match on (for example, combining an employee ID and a month into one text string), then searching for that same concatenation as the lookup_value — functional, but it means permanently modifying the source data just to support the lookup. XLOOKUP handles this more directly without a helper column: multiplying two boolean array conditions together inside the lookup_array argument, like =XLOOKUP(1, (A:A="E104")*(B:B="March"), C:C), which evaluates to 1 only on the row where both conditions are true and 0 elsewhere, then looks up that 1. It's a genuinely more advanced pattern than a single-condition lookup, but it's one more real case where XLOOKUP avoids a workaround VLOOKUP requires.

Wildcard matching and large-dataset performance

Both functions support wildcard characters — * for any sequence of characters, ? for a single character — when the match is set to exact rather than approximate, which is genuinely useful for partial-text lookups like finding a row where a product code merely starts with a known prefix. VLOOKUP needs FALSE explicitly set for wildcards to apply at all, consistent with its general exact-match requirement; XLOOKUP supports them by default since exact match is already its default behavior. On very large datasets — tens of thousands of rows or more — XLOOKUP's optional binary search mode (set via its search_mode argument, for data already sorted ascending or descending) can meaningfully outperform VLOOKUP's linear top-to-bottom search, though for typical spreadsheet sizes in everyday use, the performance difference between the two is negligible and shouldn't be the deciding factor over the correctness and safety differences covered above.

Returning more than one column at once

A single VLOOKUP call returns exactly one value from one column, so pulling back three different columns for the same lookup value traditionally means writing three separate VLOOKUP formulas, each with its own col_index_num. XLOOKUP can return an entire row or range in one call by passing a multi-column range as the return_array — =XLOOKUP("E104", A:A, B:D) returns all three columns B through D for the matching row as a single spilled array, in Excel versions that support dynamic arrays. This is a meaningful reduction in formula duplication for anything that needs to pull several related fields for the same lookup value, and it's another case where XLOOKUP's separated lookup/return-range design pays off beyond just the left-lookup and column-safety benefits already covered.

A quick way to decide

If you know for certain the spreadsheet will only ever be opened in Excel 365/2021+ or a modern Google Sheets file, XLOOKUP is generally the better default — it's safer against column insertions, defaults to the match type most lookups actually want, and handles left-side lookups directly. If the file needs to work in an older Excel install, LibreOffice, or any environment you're not fully certain about, VLOOKUP (written carefully, with FALSE as the fourth argument) remains the safer, universally-compatible choice. Either way, the fix for VLOOKUP's most common real bug — an unintended approximate match — is simple: always pass FALSE explicitly rather than relying on the default.