VLOOKUP vs XLOOKUP vs INDEX+MATCH vs FILTER — When to Use Each

Excel has multiple ways to perform lookups. They solve similar problems, but each is better in different situations.

Quick Comparison

Important Behavior (All Lookups)

By default:

• XLOOKUP
• VLOOKUP
• INDEX+MATCH

…return the first occurrence of a match.

You can make XLOOKUP return the last occurrence using:

search_mode = -1

But if you need every match, none of these are correct → use FILTER.

Use XLOOKUP (Default Choice)

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

Use when:

• You’re in Excel 365 / 2021+
• You want the cleanest, simplest formula
• You don’t want to count column indexes
• You want better built-in error handling

XLOOKUP is designed to replace VLOOKUP and HLOOKUP.

Use VLOOKUP (Legacy Only)

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

Use when:

• You’re maintaining old spreadsheets
• XLOOKUP is not available

Limitations:

• Lookup column must be first
• Breaks when columns move
• Requires column index numbers

Use INDEX + MATCH (Power + Compatibility)

=INDEX(D:D, MATCH(A2, A:A, 0))

Use when:

• You’re in Excel 2019 or earlier
• You need flexibility VLOOKUP can’t provide
• You want a dynamic lookup that doesn’t break when columns move
• You’re working with large datasets (50,000+ rows)

INDEX+MATCH works in every version of Excel and does not require Microsoft 365.

Functionally, INDEX+MATCH and XLOOKUP achieve the same result in different ways.

Use FILTER (Return All Matches)

=FILTER(A:D, A:A=A2)

Use when:

• A value appears multiple times
• You need every matching row returned
• You’re in Excel 365 / 2021+

This is something XLOOKUP, VLOOKUP, and INDEX+MATCH cannot do.

Summary — When to Use Each

• XLOOKUP → Modern, easiest, default choice
• VLOOKUP → Only for legacy files
• INDEX+MATCH → Best for compatibility and flexibility
• FILTER → When you need multiple matching results

For modern Excel: XLOOKUP first, FILTER when needed.
For older Excel: INDEX+MATCH over VLOOKUP.