What is the difference between VLOOKUP and INDEX-MATCH?

Microsoft Excel - Interview Questions and Answers

Both VLOOKUP and INDEX-MATCH are used for searching and retrieving data, but INDEX-MATCH is more powerful and flexible. Here’s how they differ:

1. VLOOKUP (Vertical Lookup)

Searches for a value in the first column and returns data from a specified column in the same row.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

ID Name Score

101 John 85

102 Alice 92

103 Bob 78
```
=VLOOKUP(102, A2:C4, 2, FALSE)
```
Result: "Alice" (Finds ID 102 in column A and returns Name from column 2).

ID	Name	Score
101	John	85
102	Alice	92
103	Bob	78

2. INDEX-MATCH (More Flexible)

INDEX and MATCH are used together to perform lookups, offering more flexibility.

INDEX retrieves data from a specific row and column.
MATCH finds the position of a lookup value.

Syntax:

=INDEX(return_range, MATCH(lookup_value, lookup_range, [match_type]))

Example:
```
=INDEX(B2:B4, MATCH(102, A2:A4, 0))
```
Result: "Alice" (Finds ID 102 in column A and returns the corresponding Name from column B).

Comparison Table

Feature	VLOOKUP	INDEX-MATCH
Lookup Direction	Only left to right	Can look left or right
Column Changes	Breaks if columns are inserted/deleted	Works dynamically
Performance	Slower for large datasets	Faster for large datasets
Flexibility	Limited to vertical lookups	Can be used for both vertical & horizontal lookups
Ease of Use	Simple and easy	Slightly complex

When to Use Which?

Use VLOOKUP if your data is simple and lookup values are always in the first column.
Use INDEX-MATCH if you need flexibility, want to search in any direction, or work with large datasets.