Three lookup functions that confuse almost everyone. Here's exactly how each works and when to use them.
VLOOKUP searches down the leftmost column of a range and returns a value from a specified column to the right. It's the function most people learn first.
=VLOOKUP(lookup_value, table_range, col_index, [match_type])
=VLOOKUP(A2, $D$2:$F$100, 2, 0)
-- Find A2 in column D, return column E (2nd column of range)VLOOKUP limitations:
MATCH doesn't return a value — it returns the position (row or column number) of a value within a range. By itself it's less useful. Combined with INDEX, it becomes very powerful.
=MATCH(lookup_value, lookup_range, [match_type])
=MATCH("London", A2:A100, 0)
-- Returns the row number where "London" appears in A2:A100
-- 0 = exact match, 1 = approximate ascending, -1 = approximate descendingINDEX returns the value at a specific row and column position within a range. Tell it where to look, and it retrieves what's there.
=INDEX(return_range, row_num, [col_num])
=INDEX(C2:C100, 5)
-- Returns the value in the 5th row of C2:C100
=INDEX(B2:D100, 5, 2)
-- Returns the value at row 5, column 2 of the range B2:D100Nest MATCH inside INDEX. MATCH finds the position, INDEX retrieves the value at that position.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(C2:C100, MATCH("London", A2:A100, 0))
-- Find "London" in column A, return the corresponding value from column C
-- Works even when column C is to the LEFT of column AThis is the key advantage over VLOOKUP — INDEX MATCH can look in any direction. The lookup column doesn't need to be on the left.
| Feature | VLOOKUP | INDEX MATCH |
|---|---|---|
| Look left of lookup column | ✗ No | ✓ Yes |
| Handles inserted columns | ✗ Breaks | ✓ Stable |
| Speed on large datasets | Slower | Faster |
| Multiple criteria lookup | Complex | ✓ Cleaner |
| Works in Excel 2010+ | ✓ Yes | ✓ Yes |
| Ease of learning | Easier | Steeper curve |
If you have Excel 365 — use XLOOKUP. It replaces both VLOOKUP and INDEX MATCH with cleaner syntax and more features. If you need compatibility with Excel 2019 or earlier — use INDEX MATCH. It handles everything VLOOKUP can and much more. Only use VLOOKUP if you're maintaining legacy files or the file will be opened by someone on very old Excel.
INDEX MATCH can look up both rows and columns simultaneously — useful for finding a value at the intersection of a row and column:=INDEX(B2:D10, MATCH("London",A2:A10,0), MATCH("Q2",B1:D1,0))
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →