Excel VLOOKUP vs XLOOKUP Feature Comparison
XLOOKUP does everything VLOOKUP does with fewer traps: it looks in any direction, defaults to an exact match, and refers to the return column directly instead of by a column number. Microsoft's own VLOOKUP help page now suggests trying XLOOKUP, describing it as an improved version that works in any direction and returns exact matches by default (Microsoft Support, VLOOKUP function, read October 9, 2026). VLOOKUP still matters, though: Microsoft notes that XLOOKUP is not available in Excel 2016 and Excel 2019 (Microsoft Support, XLOOKUP function), so workbooks shared with people on those versions may still need VLOOKUP or INDEX/MATCH. This guide compares the two side by side and shows when to use which.
How VLOOKUP Works
The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Microsoft's documentation sets out the key rules:
- The lookup value must be in the first column of the table range you give it.
- You return a value by column number, counting from the first column of the range.
- The last argument chooses approximate match (1/TRUE) or exact match (0/FALSE). Approximate match is the default if you leave it out, and it assumes the first column is sorted.
Those rules cause the classic VLOOKUP mistakes: forgetting FALSE and getting a wrong "closest" answer from unsorted data, a column number that points at the wrong column after someone inserts a column, and the inability to look to the left of the lookup column without rearranging data or switching to INDEX/MATCH.
How XLOOKUP Works
The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). According to Microsoft:
- You give the column (or row) to search and the column (or row) to return separately, so the return range can be on either side.
- match_mode defaults to exact match; other options return the next smaller or next larger item, or use wildcards.
- if_not_found lets you show your own text instead of #N/A.
- search_mode defaults to searching from the first item; you can search from the last item (useful for finding the most recent entry) or use binary search on sorted data.
Because the return range is a reference rather than a number, inserting a column between the lookup and return ranges does not break the formula.
Side-by-Side Comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match | Approximate (unless FALSE) | Exact |
| Direction | Lookup column must be leftmost | Any direction, rows or columns |
| Return reference | Column number | Range reference |
| Not-found handling | Wrap in IFERROR or IFNA | Built-in if_not_found argument |
| Search from the end | No | Yes (search_mode) |
| Availability | All Excel versions | Not in Excel 2016 or 2019, per Microsoft |
Examples
Exact lookup of a price
VLOOKUP: =VLOOKUP(A2, Products!A:D, 4, FALSE) returns the fourth column of the Products table for the part number in A2. XLOOKUP: =XLOOKUP(A2, Products!A:A, Products!D:D, "Not found") does the same and shows "Not found" when the part number is missing.
Looking to the left
If employee IDs are in column C and names in column A, VLOOKUP cannot return the name without rearranging columns. XLOOKUP handles it directly: =XLOOKUP(F2, C:C, A:A).
Most recent entry
To find the last time a customer appears in a log, use XLOOKUP with search_mode -1: =XLOOKUP(F2, B:B, D:D, , 0, -1).
Returning several columns at once
Because the return range can be more than one column, a single XLOOKUP can return a whole row of related values. For example, =XLOOKUP(A2, Products!A:A, Products!B:D) returns the name, category and price for the part number in A2 into three adjacent cells. In versions that support dynamic arrays, the result spills automatically; make sure the cells to the right are empty, or Excel shows a #SPILL! error.
Making Lookups Sturdier With Excel Tables
Whichever function you use, converting your data range into an Excel Table (Insert > Table) makes lookups easier to maintain. Tables expand automatically when you add rows, so formulas that refer to them keep covering all the data without you editing the ranges. Structured references such as Products[PartNo] and Products[Price] also make formulas readable: =XLOOKUP(A2, Products[PartNo], Products[Price], "Not found") says exactly what it does. With VLOOKUP, a table reference keeps the range current, but the column number still needs care if columns are rearranged.
Clean data matters as much as the formula. Lookups fail when the same ID is stored as a number in one sheet and as text in another, or when values carry leading or trailing spaces from an export. Fix those at the source, or use helper columns with TRIM and VALUE, before blaming the lookup function.
When to Keep Using VLOOKUP
- The workbook will be opened in Excel 2016 or Excel 2019.
- You are maintaining an existing model and changing formulas would add risk without benefit.
- A colleague's tools or templates expect VLOOKUP.
In those cases, protect yourself by always entering FALSE for exact matches, using table references (structured references) so ranges grow with the data, and wrapping the formula in IFNA to handle missing values clearly. INDEX with MATCH is another option that works in older versions and can look in any direction.
Migrating a Workbook From VLOOKUP to XLOOKUP
- Confirm everyone who uses the file has a version that supports XLOOKUP.
- Make a copy of the workbook before changing formulas.
- Replace formulas one sheet at a time, starting with the ones most likely to break, such as those with approximate matches on unsorted data.
- Compare results between the old and new columns for a sample of rows before deleting the old formulas.
- Use the if_not_found argument to make missing values visible instead of hiding them.
Common Errors and Fixes
- #N/A: the value isn't found. Check for extra spaces (TRIM), numbers stored as text, or a wrong range.
- Wrong result from VLOOKUP: the last argument is missing, so it used approximate match. Add FALSE.
- #REF! in VLOOKUP: the column number is larger than the table range.
- #VALUE! in XLOOKUP: the lookup and return arrays are different sizes.
Frequently Asked Questions
Can VLOOKUP search to the left?
No. Microsoft's documentation says the lookup value must be in the first column of the range. Use XLOOKUP or INDEX/MATCH instead.
Which Excel versions have XLOOKUP?
Microsoft states that XLOOKUP is not available in Excel 2016 or Excel 2019. Current Microsoft 365 versions include it; check the "Applies to" list on Microsoft's XLOOKUP page for your version.
Is XLOOKUP faster than VLOOKUP?
Speed depends on the data and formula design. For most everyday workbooks the difference is not noticeable; accuracy and maintainability are the main reasons to switch. For very large sorted data, XLOOKUP's binary search option can help.
Does Google Sheets support XLOOKUP?
Check Google's Sheets function list for current support before sharing a workbook that relies on it.
Key Takeaways
- XLOOKUP defaults to exact match, looks in any direction and handles missing values with if_not_found.
- VLOOKUP defaults to approximate match, so always add FALSE for exact lookups.
- XLOOKUP is not available in Excel 2016 or 2019; keep VLOOKUP or INDEX/MATCH for files shared with those versions.
- Migrate in steps and compare results before removing old formulas.
Sources and References
- Microsoft Support, VLOOKUP function (read October 9, 2026).
- Microsoft Support, XLOOKUP function (read October 9, 2026).