Excel: VLOOKUP vs XLOOKUP vs INDEX/MATCH

Question

I use VLOOKUP everywhere but it breaks when someone inserts a column. Is XLOOKUP better?

Accepted 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.

1 Repons