VLOOKUP versus INDEX/MATCH is one of the oldest debates in spreadsheet culture. Both find a value in one place and return a corresponding value from somewhere else. The difference is in what each can do and what each is likely to break.
What VLOOKUP Does
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table range and returns a value from a column to the right. The function takes four arguments: the value to look up, the table range, the column number to return from, and an exact-match flag (use 0 or FALSE for exact match).
The column number is the critical one. If you want the value from the third column of your table, you write 3. If someone inserts a column between the lookup column and the result column, your VLOOKUP silently returns the wrong column. The formula breaks without producing an error.
What INDEX/MATCH Does
INDEX and MATCH are two separate functions used in combination. MATCH finds the position (row number) of the lookup value within a column. INDEX returns the value at a specified row position within another column. Because you're referencing columns by name rather than by number, inserting columns doesn't break the formula.
INDEX/MATCH also looks in any direction — you can look left, right, or even perform two-dimensional lookups with a nested MATCH. VLOOKUP can only look to the right.
Upload your spreadsheet and get an instant quality report — free with Sohovi — try Sohovi free.
When VLOOKUP Is Fine
Use VLOOKUP when:
- The spreadsheet is simple and column insertions are unlikely
- The person maintaining the spreadsheet is less experienced (VLOOKUP is easier to read and debug)
- You're doing a quick analysis that won't be maintained long-term
When INDEX/MATCH Is Better
Use INDEX/MATCH when:
- The table structure may change (columns get inserted or reordered)
- You need to look to the left of the lookup column
- You're doing a two-dimensional lookup (matching on both row and column)
- The formula will be maintained by people who understand Excel
The Modern Answer: XLOOKUP
If you're using Microsoft 365 or Excel 2021+, XLOOKUP renders this debate largely moot. XLOOKUP has the simplicity of VLOOKUP (one formula, not a nested combination) with the flexibility of INDEX/MATCH (works in any direction, uses column references not numbers). It also accepts a default value argument instead of requiring IFERROR wrapping.
Learn VLOOKUP to understand legacy spreadsheets. Learn INDEX/MATCH for older Excel versions. Learn XLOOKUP as your default going forward.
Sohovi gives you a full quality report on any spreadsheet in seconds — upload your file and see exactly what needs fixing.
For any formula you encounter that you don't fully understand, paste it into Sohovi's free Excel Formula Explainer — it breaks down VLOOKUP, INDEX/MATCH, XLOOKUP, and dozens of other functions into plain English.
Keep Reading
Explain Any Excel Formula in Plain English →