INDEX MATCH vs VLOOKUP: what is the difference?

The two syntaxes

VLOOKUP takes four arguments: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup value must sit in the first column of the table, the third argument counts columns from the left edge of the table, and the fourth decides between exact match (FALSE or 0) and approximate match (TRUE, 1 or left out).

INDEX MATCH nests two functions. MATCH(lookup_value, lookup_array, [match_type]) returns a position: K-330 is the 4th entry in A2:A7, so =MATCH(F9,A2:A7,0) returns 4. INDEX(array, row_num, [column_num]) returns the value at a position: the 4th value of E2:E7 is 70. Put together, =INDEX(E2:E7,MATCH(F9,A2:A7,0)) reads as "return the value from column E in the row where column A equals F9". The lookup column and the return column are two separate ranges, and that is where every advantage below comes from.

Step by step: the same lookup both ways

With VLOOKUP you select the whole block A2:F7 and count columns: SKU is 1, Product 2, Category 3, Jan 4, Feb 5. The formula =VLOOKUP(F9,A2:F7,5,FALSE) returns 70.

With INDEX MATCH you point at the two columns directly. First the position: MATCH(F9,A2:A7,0) = 4. Then the value: INDEX(E2:E7,4) = 70. Both ranges must have the same size and start in the same row, otherwise the position points at the wrong record. Lock them with $ (for example $E$2:$E$7) before you fill the formula down a list.

A useful habit in large sheets: put the MATCH in its own helper column and let several INDEX formulas reuse that position. One search then feeds the price, the stock and the description, which makes the sheet easier to audit and saves recalculation work.

Difference 1: inserted and deleted columns

The column number in VLOOKUP is a typed constant. Insert a "Dec" column between Category and Jan and =VLOOKUP(F9,A2:G7,5,FALSE) quietly returns January instead of February: no error, just the wrong number. INDEX MATCH refers to column E itself, so Excel moves the reference to F when a column is inserted and the result stays correct. For models that other people edit, this is the strongest argument for INDEX MATCH.

Worked example: one sales table, VLOOKUP and INDEX/MATCH side by side

Monthly units for six outdoor products. Change the SKU in F9 or the month in F10 and every lookup below updates.

Worked example: one sales table, VLOOKUP and INDEX/MATCH side by side

	A	B	C	D	E	F
1	SKU	Product	Category	Jan	Feb	Mar
2	K-210	Trail backpack 30L	Bags	140	162	175
3	K-215	Hip pack	Bags	88	95	120
4	K-320	Down jacket	Apparel	64	41	22
5	K-330	Rain shell	Apparel	52	70	96
6	K-410	Headlamp	Gear	210	198	230
7	K-420	Water filter	Gear	75	83	110
8
9	SKU to look up					K-330
10	Month to look up					Mar
11	Feb units with VLOOKUP (column number 5, exact match)					70
12	Feb units with INDEX/MATCH (return column E)					70
13	Row position of the SKU (MATCH alone)					4
14	Units for the month in F10 (INDEX/MATCH/MATCH)					96
15	SKU of "Headlamp" (lookup to the left)					K-410
16	Unknown SKU K-999, INDEX/MATCH with IFNA					Not found
17	Best-selling product in March					Headlamp

Fictitious products and sales figures. The formulas work the same in Excel 2010 and later, Microsoft 365 and Google Sheets.

Difference 2: looking to the left

VLOOKUP can only return columns to the right of the lookup column. To find the SKU of the product named "Headlamp", VLOOKUP would need the table rearranged. INDEX MATCH does not care about the order: =INDEX(A2:A7,MATCH("Headlamp",B2:B7,0)) searches column B and returns K-410 from column A. The same idea finds the product with the best March sales, =INDEX(B2:B7,MATCH(MAX(F2:F7),F2:F7,0)), which returns Headlamp.

Difference 3: two-way lookups

INDEX accepts a row and a column number, so two MATCH functions can pick both. =INDEX(D2:F7,MATCH(F9,A2:A7,0),MATCH(F10,D1:F1,0)) finds K-330 in row 4 and "Mar" in column 3 of the month headers and returns 96. Change F10 to "Jan" and the result becomes 52 without touching the formula. With VLOOKUP you would need MATCH inside the column argument anyway, =VLOOKUP(F9,A2:F7,MATCH(F10,A1:F1,0),FALSE), which already is half of an INDEX MATCH.

Exact match, approximate match and errors

Both functions default to an approximate match: VLOOKUP when the fourth argument is left out, MATCH when the third is left out. On an unsorted list that returns a wrong value instead of an error. For codes, names and IDs always type FALSE in VLOOKUP and 0 in MATCH. Approximate match is meant for sorted band tables such as tax brackets or discount tiers.

When the value is missing, both return #N/A. Wrap the formula in IFNA to show a message: =IFNA(INDEX(E2:E7,MATCH("K-999",A2:A7,0)),"Not found"). The #N/A error guide covers the usual causes, such as trailing spaces and numbers stored as text.

Which one should you use?

Use VLOOKUP for a quick, one-off lookup in a small table whose columns will not move, or when the file must stay readable for colleagues who only know VLOOKUP. Use INDEX MATCH in models that grow, when the result column sits left of the key, for two-way lookups and when one position should feed several formulas. If everyone has Microsoft 365, Excel 2021 or later, XLOOKUP combines the strengths of both in one function. For keys spread over several columns, see INDEX MATCH with multiple criteria.

Common mistakes

• Leaving out the match type. MATCH(F9,A2:A7) uses approximate match and can return a wrong row on unsorted data. Type 0 for an exact match.
• Ranges of different size. INDEX(E2:E8,MATCH(F9,A3:A8,0)) is shifted by one row and returns the neighbour's value. Start and end both ranges in the same rows.
• Missing dollar signs. Filled down, A2:A7 becomes A3:A8, and the last records drop out of the search. Use $A$2:$A$7.
• Counting VLOOKUP columns from column A of the sheet. The column number counts from the left edge of the table range, not of the sheet.
• Lookup value and lookup column in different formats. 1001 as a number does not match "1001" stored as text, so both formulas return #N/A.