Excel: VLOOKUP vs XLOOKUP vs INDEX/MATCH
asked 4 months ago478 viewsen
I use VLOOKUP everywhere but it breaks when someone inserts a column. Is XLOOKUP better?
AI diagnosis
AI-generated. Not an answer — the community below confirms or corrects it. Always verify before relying on it.
1 Answer
Yes. If everyone uses Microsoft 365 or Excel 2021+, XLOOKUP is the better default:
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")Why: - It references the return column directly, so inserting columns doesn't break it (VLOOKUP uses a column *number*). - It can look left, and it's exact match by default (VLOOKUP defaults to approximate, a classic source of silent errors). - It has a built-in "if not found" value, can search from the bottom, and can return multiple columns.
INDEX/MATCH gives the same robustness and works in older Excel versions, so use it if the file is shared with Excel 2016 users.
Bonus: turn your ranges into Tables (Ctrl+T) and use structured references like Products[Price]. Formulas become readable and auto-expand with new rows.
Sign in to tell others whether this worked.
Sign in and verify your email to post an answer.