XLOOKUP vs VLOOKUP in Excel (The Right Way to Do Lookups)

If XLOOKUP exists in your version of Excel, you should almost always use it instead of VLOOKUP. It’s simpler, safer, and doesn’t break when your table changes.

Simple Example Table

Goal: Look up a Product Name and return Boxes Shipped.

Assume the product you’re searching for is in cell F2.

The VLOOKUP Way (Old Method)

=VLOOKUP(F2, A:D, 4, FALSE)

• F2 → product name to find
• A:D → table range
• 4 → Boxes Shipped column
• FALSE → exact match

The Problem with VLOOKUP

• Lookup column must be first
• You must count column positions
• Insert a column → formula breaks
• Not flexible

The XLOOKUP Way (Modern Method)

=XLOOKUP(F2, A:A, D:D)

• Lookup column does not need to be first
• No column index numbers
• Easier to read and maintain

Best Practice: Convert to an Excel Table (Ctrl + T)

Convert the data into a Table. Assume the table is named Table1.

Best Formula You Can Write

=XLOOKUP(F2, Table1[Product Name], Table1[Boxes Shipped])

Why this is best:

• Expands automatically when rows are added
• Doesn’t break if columns move
• Uses names instead of positions
• Fully dynamic

This is what advanced Excel users do.

If You Are Forced to Use VLOOKUP

You can still improve it with structured references:

=VLOOKUP(F2, Table1, 4, FALSE)

But you still depend on column positions.

Strong Alternative: INDEX + MATCH

=INDEX(Table1[Boxes Shipped], MATCH(F2, Table1[Product Name], 0))

Dynamic and very reliable.

References: Relative vs Absolute vs Table

Press F4 to make a reference absolute.

Common Lookup Errors

Key Takeaways

• Use XLOOKUP, not VLOOKUP
• Convert data to a Table
• Use structured references
• Avoid column index guessing
• INDEX/MATCH is still excellent