XLOOKUP vs VLOOKUP: Differences and When to Use Each

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The xlookup vs vlookup decision usually comes down to three things: what happens when no match exists, which direction you can look, and what breaks when someone inserts a column. VLOOKUP is available in every modern version of Excel and works well for simple rightward lookups. XLOOKUP is available in Microsoft 365 and Excel 2021 and later, defaults to exact matching, and can return values from any column. This article compares the two functions on syntax, defaults, error handling and column-insertion behavior, then shows both returning the same result on a small product table.

Quick Answer

• Syntax: VLOOKUP takes four arguments, =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). XLOOKUP takes three required arguments, =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
• Default matching: VLOOKUP defaults to approximate matching when you omit the fourth argument, which is a common source of wrong answers. XLOOKUP defaults to exact matching.
• Direction: VLOOKUP can only return values to the right of the lookup column. XLOOKUP can return values from any column, left or right.
• Missing values: VLOOKUP returns #N/A when nothing matches. XLOOKUP accepts an if_not_found argument so you can return a blank, a zero or a custom message.
• Column insertion: VLOOKUP breaks when a column is inserted inside its table range because the hard-coded column index no longer points at the right data. XLOOKUP references the return column directly, so it survives insertions and deletions.

Key Differences

The availability row matters most in practice. If you share workbooks with people on older Excel versions, XLOOKUP formulas will not calculate for them. VLOOKUP has no such constraint.

VLOOKUP Explained

VLOOKUP searches the first column of a range and returns a value from a column you specify by number. The general form is:

$$=\text{VLOOKUP}(\text{lookup\_value},\ \text{table\_array},\ \text{col\_index\_num},\ [\text{range\_lookup}])$$

The col_index_num is counted from the left edge of table_array. If your table starts in column A and the price sits in column C, the index is 3. That number is the function's main weakness. It is a position, not a name, so it does not know what data it points at.

The fourth argument controls matching. Set it to FALSE (or 0) for an exact match. Set it to TRUE (or 1) for an approximate match, which requires the first column to be sorted in ascending order and returns the largest value less than or equal to the lookup value. Omitting the argument is the same as TRUE, which is why unsorted data plus a missing fourth argument produces quietly wrong numbers instead of an error. For a fuller walkthrough of the argument list, see VLOOKUP in Excel: Formula, Syntax and Examples.

VLOOKUP also cannot look left. If the value you want sits to the left of the lookup column, VLOOKUP cannot reach it without rearranging the sheet or nesting another function.

XLOOKUP Explained

XLOOKUP separates the column you search from the column you return. The general form is:

$$=\text{XLOOKUP}(\text{lookup\_value},\ \text{lookup\_array},\ \text{return\_array},\ [\text{if\_not\_found}],\ [\text{match\_mode}],\ [\text{search\_mode}])$$

The lookup_array and return_array must be the same size, but they do not need to be adjacent and they do not need to be in any particular order. That single change removes the column index and the left-lookup restriction at the same time.

Matching defaults to exact, so =XLOOKUP(104, A2:A5, C2:C5) finds an exact 104 without any extra argument. The match_mode argument adds options beyond exact and approximate, including wildcard matching. The search_mode argument lets you search from the last entry backward, which is useful when a list contains duplicates and you want the most recent one. The if_not_found argument replaces the #N/A error with whatever you choose, such as "" for a blank cell or "Not found" for a readable label. A step-by-step walkthrough is in How to Use XLOOKUP in Excel (Step by Step).

Worked Example

The table below holds four products with an ID, a name and a price. Column D uses VLOOKUP, column E uses XLOOKUP to return the price, and column F uses XLOOKUP to return the product name from a column to the left of the lookup column. Every formula looks up ID 104.

Three things are worth reading off this sheet.

1. VLOOKUP with exact match returns the price for ID 104 from the third column. The FALSE argument is doing the work here. Drop it and the formula falls back to approximate matching.
2. XLOOKUP defaults to exact match and returns the price for ID 104. No fourth argument is needed because exact is the default.
3. XLOOKUP can look left and return the product name for ID 104. VLOOKUP cannot do this with the same layout, because Product Name sits in column B, to the left of the lookup column A.

Now change the sheet. Insert a new column between Product ID and Product Name. The VLOOKUP formula still says column 3, but column 3 is no longer Price, so it returns the wrong field. The XLOOKUP formula points at $C$2:$C$5 by reference, and Excel adjusts that reference when the column moves, so it keeps returning the price. This is the column-insertion behavior that drives most migrations from VLOOKUP to XLOOKUP. For a wider comparison that includes INDEX-MATCH, see Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH.

Which One Should You Use?

Use XLOOKUP when you can. It defaults to exact matching, looks in both directions, handles missing values without an error wrapper, and does not break when columns move. For most new work in Microsoft 365 or Excel 2021 and later, it is the simpler and safer choice.

Use VLOOKUP when compatibility is the priority. If the workbook is opened by people on older Excel versions, or if it feeds a system that does not recognize newer functions, VLOOKUP will calculate everywhere. It is also fine for quick rightward lookups on small, stable tables where the column order will not change.

A practical middle path: build with XLOOKUP, and if you later need to share the file with older Excel users, replace those formulas with VLOOKUP or INDEX-MATCH at that point. If you need to find the position of a value instead of the value itself, Excel MATCH Function: Syntax and Examples covers that separate job. If your data is laid out in rows instead of columns, How to Use HLOOKUP in Excel (With Examples) is the horizontal equivalent.

Common Mistakes

• Omitting the fourth VLOOKUP argument. The formula then uses approximate matching and can return a plausible but wrong value. Always pass FALSE for exact matching.
• Hard-coding a column index that later shifts. Inserting or deleting a column inside the table range changes what the index points at. Use XLOOKUP with a return range, or convert the table to an Excel Table so structured references update.
• Assuming XLOOKUP works for everyone. It requires Microsoft 365 or Excel 2021 and later. Older versions return a #NAME? error. Check who opens the file before you commit to it.
• Leaving #N/A visible in reports. Wrap VLOOKUP in IFERROR, or use the if_not_found argument in XLOOKUP, so missing IDs show a blank or a readable label instead of an error.
• Mismatched lookup and return ranges in XLOOKUP. The two ranges must be the same size. If one is longer, XLOOKUP returns a #VALUE! error.
• Mixing text and numbers in the lookup column. An ID stored as text ("104") will not match a numeric 104. Check the cell format on both sides before debugging the formula.

Limitations

Neither function can return more than one match on its own. If a lookup value appears several times, VLOOKUP returns the first match and XLOOKUP returns the first match by default, or the last if you set search_mode to search backward. Getting all matches requires different tools.

VLOOKUP's approximate mode also misleads when the first column is unsorted. It does not raise an error, it can return a value from an unrelated row, which can look like a valid answer. XLOOKUP's exact default avoids that trap, but its approximate mode has its own requirements, so read the argument carefully before using it.

Both functions slow down on very large ranges because each formula scans the lookup array. On sheets with tens of thousands of rows, a helper column with a unique key, or a different lookup strategy, is often faster than a full-column reference.

Frequently Asked Questions

Is XLOOKUP always better than VLOOKUP?

No. XLOOKUP is better on defaults, direction and error handling, but it is not available in older Excel versions. If your workbook must open for users on those versions, VLOOKUP is the safer choice. For new work in Microsoft 365, XLOOKUP is usually the better default.

Does VLOOKUP default to exact or approximate match?

It defaults to approximate match. If you leave out the fourth argument, VLOOKUP behaves as if you passed TRUE, which requires the first column to be sorted ascending. Always pass FALSE when you want an exact match.

Can XLOOKUP look to the left?

Yes. You give XLOOKUP a lookup array and a separate return array, and the return array can sit to the left of the lookup array. VLOOKUP cannot do this, because it always returns a value from a column to the right of the lookup column.

What happens to VLOOKUP when I insert a column?

The column index stays the same number but points at a different column, so the formula returns the wrong field. XLOOKUP references the return column directly, and Excel adjusts that reference when columns are inserted or deleted, so it keeps returning the correct data.

Can I use XLOOKUP and VLOOKUP in the same workbook?

Yes. Both functions can coexist in one workbook. The formulas calculate independently, so you can migrate sheet by sheet. The only constraint is that any sheet using XLOOKUP must be opened in a version that supports it.

References

This article draws on the standard references listed under Further Reading.

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning
• McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis
• Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology
• Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology

Related Articles

• Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH
• VLOOKUP in Excel: Formula, Syntax and Examples
• How to Use XLOOKUP in Excel (Step by Step)
• How to Use HLOOKUP in Excel (With Examples)
• CDF vs PDF: Differences and When to Use Each