XLOOKUP vs VLOOKUP: which lookup formula should you use?

Most lookup debates become too theoretical. In day-to-day spreadsheet work, the decision is usually about maintenance risk. If you are building a new Excel file for people on current Microsoft 365 or modern Excel, XLOOKUP is normally the better default. It separates the lookup range from the return range, supports a custom message when no match is found, and does not break just because someone inserts a column inside the table.

VLOOKUP is still useful because many teams already understand it and many older files rely on it. If a workbook is shared with people on older Excel versions, or if the surrounding model already uses VLOOKUP everywhere, replacing one formula with XLOOKUP can create more confusion than value. The best spreadsheet formula is not always the newest one. It is the one the file can support safely.

VLOOKUP uses a table range and a column number. That column number is compact, but it hides meaning. A formula such as VLOOKUP(A2,A:D,4,FALSE) works only if the fourth column in the selected range is still the field you intended to return. If someone inserts a new column, the formula may keep calculating while returning the wrong field.

XLOOKUP asks for a lookup array and a return array. That makes the formula longer in some cases, but the intent is visible. XLOOKUP(A2,Products!A:A,Products!D:D,"Not found") tells the reviewer exactly where the key lives and where the returned value comes from.

Do not convert formulas just because XLOOKUP is available. Start by understanding what the existing formula returns, whether it uses exact match, and whether any downstream formulas depend on its error behavior. A clean migration keeps the same lookup key, return field, and behavior when no match is found unless you are intentionally improving them.

A good migration workflow is simple: explain the original VLOOKUP, write the equivalent XLOOKUP, test several known matches, test one missing key, and then compare totals or downstream outputs. If the workbook feeds a report, keep both formulas side by side for a short review period before replacing the old one.

When a lookup fails, the formula is not always the problem. Extra spaces, inconsistent ID formats, hidden characters, duplicate keys, and mixed text-number values can all create confusing results. Before rewriting formulas, inspect the key column and the lookup value. A perfect XLOOKUP will still fail if one side stores 00123 as text and the other stores 123 as a number.

For important files, document the lookup key rule near the table: one row per key, no blanks, no duplicates, consistent format. That small note often prevents more errors than a more complex formula change.