Xlookup vs vlookup?

For most new Excel workbooks, use XLOOKUP: it defaults to exact matches, can return results from either side of the lookup column, and lets you specify what to show when nothing matches. VLOOKUP remains useful when a workbook must work in older Excel versions, but its fixed column-number approach makes it less flexible and easier to break.

The core difference

Both functions search for a value in one range and return a related value from another. The important difference is how you tell Excel where to look and what to return. VLOOKUP searches the first column of a rectangular table and returns a value from a column to its right, identified by a number. XLOOKUP takes a lookup range and a return range separately, so those ranges can be in different positions and need not be part of one table block.

That distinction affects everyday spreadsheet work. If you insert a column into a VLOOKUP table, the column number in the formula may no longer point to the intended result. XLOOKUP refers directly to the return range, so inserting an unrelated column between the lookup and return ranges usually does not require changing the formula.

How VLOOKUP works

VLOOKUP has four arguments: the value to find, the table range, the number of the result column within that range, and whether to use an approximate match. The column number counts from the left edge of the selected range—not from the worksheet’s column letters. For example, in a range starting at column B, column number 2 means worksheet column C.

For an exact match, the fourth argument should be FALSE (or 0). If it is omitted, VLOOKUP uses approximate matching, which is a frequent source of unexpected results. Approximate matching is intended for ordered data such as tax brackets or score bands; the first column must be sorted in ascending order for reliable results.

VLOOKUP is widely used and is available in older Excel versions, making it practical for files shared with people whose software may not support newer functions. Its limitations are manageable in simple, stable tables, but the formula depends on the table’s layout and requires the lookup column to be at the left.

How XLOOKUP works

XLOOKUP separates the lookup array from the return array. Its basic arguments are the value to find, the range to search, and the range from which to return a result. Optional arguments let you define a missing-value message, choose a match mode, and choose a search direction. With no match-mode argument, it looks for an exact match; with no search-mode argument, it searches from the first item to the last.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can search vertically or horizontally, return a value to the left or right of the lookup range, and return multiple adjacent columns when the return range contains multiple columns. Those results can spill into neighboring cells, so make sure the spill area is empty.

The function’s flexibility does not remove the need for clean data. A value stored as text may not match an apparently identical number stored as a number, and extra spaces or inconsistent spelling can also prevent a match. XLOOKUP makes the lookup structure clearer, but it cannot infer that two different values were meant to be the same.

A side-by-side exact-match example

Assume product IDs are in A2:A5, product names are in B2:B5, prices are in C2:C5, and the ID to search for is in E2. If E2 contains P-103 and that product’s price in C4 is 18.50, this XLOOKUP returns 18.50:

With product IDs in A2:A5, prices in C2:C5, and the search ID in E2, enter: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")

The equivalent exact-match VLOOKUP uses the full table range and specifies that the third column of that range contains prices:

With product IDs in A2:A5, names in B2:B5, prices in C2:C5, and the search ID in E2, enter: =VLOOKUP(E2,A2:C5,3,FALSE)

Both formulas return 18.50 for P-103. The XLOOKUP formula states the result range directly, while VLOOKUP relies on the number 3. If the table later changes, that number may need attention; the separate XLOOKUP ranges make the intended relationship more visible.

Looking left, right, and across rows

VLOOKUP can only return a result from a column to the right of its lookup column. A common workaround is to rearrange the source data, create a helper column, or use another function. XLOOKUP can instead search a price column and return the corresponding ID from a column to its left.

With prices in C2:C5, product IDs in A2:A5, and the price to find in E2, this formula returns P-103 when E2 is 18.50: =XLOOKUP(E2,C2:C5,A2:A5,"Not found")

XLOOKUP also works horizontally: use a row of lookup values as the lookup array and a corresponding row of results as the return array. VLOOKUP is specifically vertical; HLOOKUP is its traditional horizontal counterpart. If a workbook is intended to be read by older Excel versions, remember that XLOOKUP itself may not calculate there even when its layout is otherwise compatible.

Exact, approximate, wildcard, and reverse searches

For ordinary IDs, names, and codes, exact matching is usually what you want. XLOOKUP uses exact matching by default. VLOOKUP needs FALSE or 0 explicitly if exact matching is intended; leaving its final argument out changes the behavior to approximate matching.

Both functions can support wildcard matching for text patterns when their respective exact/wildcard options are used. In XLOOKUP, match mode 2 enables wildcards such as * for any sequence of characters and ? for one character. For example, a search pattern beginning with a department prefix can match a longer text value. Wildcards are not a substitute for checking data quality: a broad pattern may match more than one item, and the first matching item is returned by a normal forward search.

XLOOKUP offers additional match and search choices. Match mode 1 finds an exact match or the next larger item; mode -1 finds an exact match or the next smaller item. Search mode -1 searches from the last item toward the first, which is helpful when a list is ordered by date and the latest matching entry should be returned. Binary search modes are also available for sorted data, but the data must be sorted in the required direction; using them on unsorted data can produce incorrect results.

VLOOKUP’s approximate mode is not identical to XLOOKUP’s approximate options. VLOOKUP with TRUE or an omitted final argument looks for the closest value that is less than or equal to the lookup value, under its ascending-sort requirement. Before using either function for ranges or thresholds, confirm the required boundary behavior with a small example.

Missing values and common errors

XLOOKUP’s optional fourth argument lets you provide a clear result when no match exists, such as “Not found.” Without that argument, an unsuccessful lookup returns #N/A. VLOOKUP also returns #N/A when it cannot find a match, so users often wrap it in IFNA or IFERROR to display a friendlier message. Avoid hiding every error automatically if errors might signal a real problem, such as a wrong range or a misspelled identifier.

Other common causes of failed lookups include leading or trailing spaces, inconsistent punctuation, duplicate keys, and numbers stored as text. Check that the lookup and source values have the same data type and format. Also verify that the lookup range and return range cover corresponding rows. With XLOOKUP, ranges of different lengths can lead to an error or an unintended setup; select aligned ranges deliberately.

Duplicate lookup values deserve special attention. By default, XLOOKUP returns the first match it finds from the beginning, while VLOOKUP also returns the first matching row. If the intended result is the latest or last record, sorting alone may not be enough: choose a deliberate search direction in XLOOKUP or redesign the data so each key identifies one record.

Compatibility and sharing workbooks

The main reason to choose VLOOKUP for a new formula is compatibility. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but earlier perpetual versions such as Excel 2019 and Excel 2016 do not support it. In an unsupported version, an XLOOKUP formula may display #NAME? rather than calculate. If a file will circulate through an organization with mixed versions, confirm the recipients’ Excel versions before adopting it.

Excel for the web and current Microsoft 365 editions support XLOOKUP. On Mac, availability depends on the Excel version and update channel, just as it does on Windows; check that the installed edition includes the function. If compatibility with older installations matters, use VLOOKUP or another function supported by the oldest target version, and test the actual workbook in that version when possible.

Changing an existing workbook from VLOOKUP to XLOOKUP is not automatically necessary. A stable, well-documented VLOOKUP may be perfectly serviceable. The stronger case for changing is when the formula is fragile, requires a leftward lookup, needs a custom missing-result message, or is being built for a modern Excel environment where maintainability matters.

Choosing the right function

Choose XLOOKUP when the workbook is used in supported Excel versions and you want clearer range references, exact matching by default, leftward returns, reverse searches, or a built-in missing-result value. It is generally the better default for new lookup formulas because its arguments describe the lookup and result ranges directly.

Choose VLOOKUP when a workbook must remain compatible with older Excel versions, when an established model already uses it reliably, or when your team’s conventions require it. Its familiar syntax is not inherently inaccurate; problems usually arise from the default approximate-match behavior, a wrong column number, or a lookup column that is not at the left edge of the selected table.

For a new Microsoft 365 workbook: prefer XLOOKUP for a straightforward exact lookup.
For a file shared with Excel 2016 or 2019 users: avoid XLOOKUP unless you provide a compatible alternative.
For approximate bands: define the boundary rule, sort requirements, and match mode before choosing a formula.
For repeated or duplicate keys: decide whether the first or last match is meaningful, and make that choice explicit.

Frequently Asked Questions

Is XLOOKUP better than VLOOKUP?
For most new formulas in current Excel, XLOOKUP is more flexible and less dependent on table layout. VLOOKUP can still be the better choice when older Excel compatibility is essential or an existing workbook already uses it reliably.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP requires Microsoft 365 or Excel 2021 and later. Excel 2019 and earlier versions do not support it, so a workbook containing the function may not calculate correctly in those versions.

Why does VLOOKUP return the wrong result?
A common cause is omitting the fourth argument, which makes VLOOKUP use approximate matching. Use FALSE or 0 for an exact match, and check that the lookup value is in the first column of the selected table and that the result-column number is correct.

Can XLOOKUP search to the left?
Yes. Set the lookup array to the column containing the value to find and the return array to the column containing the desired result. The return column can be on either side of the lookup column.

Can I replace every VLOOKUP with XLOOKUP?
Not safely without checking compatibility and behavior. Confirm the target Excel versions, whether each formula uses approximate matching, how duplicates should be handled, and whether the existing formulas rely on a particular table layout.

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.