how to

Migrate VLOOKUP and HLOOKUP Formulas to XLOOKUP

· 2 min read spreadsheetsxlookupvlookupformulasmigration

XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH combinations with one function that looks in any direction, defaults to exact matching, and handles misses natively. Migration is mostly mechanical once you know the argument mapping.

What you need

  • Excel 365, Excel 2021+, or Google Sheets (as of 2022)
  • A workbook containing VLOOKUP, HLOOKUP, or INDEX/MATCH formulas
  • Awareness that files saved to .xls or opened in older Excel will break on XLOOKUP

Step by step

  1. Inventory existing lookups: search formulas for VLOOKUP(, HLOOKUP(, INDEX(. Count them per sheet so you can verify nothing is skipped.
  2. Learn the mapping. =VLOOKUP(A2, Data!A:F, 3, FALSE) becomes =XLOOKUP(A2, Data!A:A, Data!C:C): lookup value, lookup array, return array. The fragile column index disappears entirely.
  3. Convert a miss handler. =IFERROR(VLOOKUP(A2, T, 3, FALSE), "n/a") collapses to =XLOOKUP(A2, T!A:A, T!C:C, "n/a") using the fourth if_not_found argument, which is cheaper than wrapping everything in IFERROR.
  4. Fix any approximate-match banding lookups deliberately: set the fifth argument match_mode to -1 (next smaller) or 1 (next larger). XLOOKUP does not require sorted data for these modes.
  5. Convert HLOOKUP by swapping arrays; XLOOKUP is direction-agnostic since you name both arrays explicitly.
  6. Exploit the return-array flexibility where it pays off: returning multiple adjacent columns with one spill formula like =XLOOKUP(A2, IDs, Data!B:D) eliminates three parallel VLOOKUPs and speeds recalculation.
  7. Search from the end when duplicates exist by setting sixth argument search_mode to -1; this cleanly replaces nested tricks used to grab the last match.
  8. Verify compatibility before rolling out: confirm every consumer opens the file in a version supporting XLOOKUP, or export a static copy for legacy readers.
  9. Spot-check migrated results against originals on a copy of the sheet using a diff column =IF(old=new,"","MISMATCH"), then delete the originals.

Common mistakes

  • Saving to .xls or sharing with users on Excel 2016, producing #NAME? errors across the workbook
  • Dropping IFERROR during migration without supplying an if_not_found default, exposing #N/A to downstream formulas
  • Assuming XLOOKUP needs sorted data for nearest-value modes, then mis-bandaging tiers that VLOOKUP previously handled via ascending sort

Related guides

If your version lacks XLOOKUP, build the same robustness with [[index-match-vs-vlookup]]. After migrating, lock the formula columns so nobody reintroduces old-style lookups, following [[protect-sheet-allow-cell-edits]].

← All guides