VLOOKUP has been the go-to lookup function in Excel for over two decades. XLOOKUP arrived as its modern replacement. If you are still using VLOOKUP out of habit, it is time to understand what XLOOKUP offers and when each function is the right choice.
How VLOOKUP works
VLOOKUP searches for a value in the first column of a range and returns a value from a specified column in the same row. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). It is simple and familiar, which is why it has been so popular. But it has real limitations.
The limitations of VLOOKUP
VLOOKUP can only search the leftmost column of your range. If your lookup value is in column C and the data you want is in column A, VLOOKUP cannot do it. You need INDEX-MATCH as a workaround. Second, the col_index_num argument is a hard-coded number. If someone inserts a column in the middle of your table, every VLOOKUP that references columns to the right of the insertion breaks silently. Third, VLOOKUP defaults to approximate match, which catches beginners off guard and produces wrong results.
How XLOOKUP solves these problems
XLOOKUP uses the syntax XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The lookup and return arrays are separate, so you can look up in any column and return from any other column, in any direction. There is no column index to break. It defaults to exact match. And it has a built-in if_not_found parameter, eliminating the need to wrap lookups in IFERROR.
When VLOOKUP still makes sense
If you are working in a shared environment where some users have older Excel versions (pre-2021 or pre-Microsoft 365), they will see errors for XLOOKUP formulas. In that case, INDEX-MATCH or VLOOKUP with FALSE as the last argument is the safer choice. Also, if you are writing quick, throwaway formulas on a well-structured table where columns will not change, VLOOKUP is perfectly fine.
When to choose XLOOKUP
For any new workbook where all users have Microsoft 365 or Excel 2021+, XLOOKUP is the better default. It handles left lookups, returns entire rows or columns, supports wildcard and reverse searches, and is easier to read. Use it for production workbooks, templates, and anything that other people will maintain.
Bonus: XLOOKUP with multiple return columns
One underused feature of XLOOKUP is returning multiple columns at once. If your return_array spans three columns, XLOOKUP will spill three values into adjacent cells. This replaces three separate VLOOKUP formulas with one XLOOKUP, making your sheet cleaner and faster.
The bottom line
If you are starting fresh and your team has modern Excel, XLOOKUP is the right choice. If you are maintaining legacy workbooks or working with mixed Excel versions, VLOOKUP with exact match is still reliable. Either way, stop using VLOOKUP with approximate match (TRUE or omitted), because that is where most lookup errors come from. Want hands-on practice? Our Excel for Professionals course covers both functions with real datasets.