What is the syntax for INDEX in Excel?

The syntax for INDEX is: =INDEX(array, row_num, [column_num])

What are common errors with INDEX?

#REF! when row/column exceeds range. #VALUE! with invalid arguments

How do I use INDEX and MATCH together?

INDEX/MATCH is a powerful lookup combination. MATCH finds the position, INDEX returns the value. Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Example: =INDEX(B:B, MATCH("Apple", A:A, 0)) finds "Apple" in column A and returns the corresponding value from column B. This works in any direction, unlike VLOOKUP.

What is the difference between INDEX/MATCH and VLOOKUP?

INDEX/MATCH advantages over VLOOKUP: 1) Can look left (VLOOKUP only looks right); 2) Faster on large datasets; 3) Won't break if columns are inserted/deleted; 4) More flexible for two-way lookups; 5) Can return values from any column. VLOOKUP is simpler for basic right-lookups. XLOOKUP (Excel 365) combines the best of both.

How do I do a two-way lookup with INDEX/MATCH?

For two-way lookups (finding a value at the intersection of a row and column), use two MATCH functions inside INDEX: =INDEX(B2:E10, MATCH(row_value, A2:A10, 0), MATCH(col_value, B1:E1, 0)). This finds the row position in the first MATCH and the column position in the second MATCH, then INDEX returns the intersecting value.

Why is INDEX/MATCH returning #N/A or #REF error?

#N/A means MATCH couldn't find the value - check for typos, extra spaces (use TRIM), or data type mismatches. #REF! means the row/column number exceeds the array size. Ensure your MATCH range and INDEX range have the same number of rows/columns. Use 0 as the third MATCH argument for exact matches.

Lookup & Reference

INDEX Function in Excel

Returns the value of an element in a table or array, selected by the row and column number indexes.

Syntax

=INDEX(array, row_num, [column_num])

Arguments

array (required): A range of cells or an array constant
row_num (required): The row in array from which to return a value
column_num (optional): The column in array from which to return a value

Examples

=INDEX(A1:C10, 5, 2) - Return value from row 5, column 2 - Result: Cell value
=INDEX(A:A, MATCH("Apple", B:B, 0)) - Combined with MATCH for lookups - Result: Matched value

Quick answer: index match excel

INDEX returns a value at a row/column position inside a range. With MATCH, it becomes a flexible lookup that is not limited to VLOOKUP’s left-column rule.
Formula: =INDEX(return_range, MATCH(lookup_value, lookup_column, 0))
Two-way: INDEX(data, MATCH(row_label,...), MATCH(col_label,...)).
Use 0 in MATCH for exact match on IDs.
Compare [INDEX MATCH vs XLOOKUP](/compare/index-match-vs-xlookup/).

INDEX in lookup formulas

Use INDEX with MATCH when you need a flexible lookup that works left, right, vertically, or horizontally.
Use INDEX to return a value at a specific row and column from a defined range.

Two-way lookup worked example

Row label in P2, column header in P3, data in B2:M20.
Formula: =INDEX(B2:M20,MATCH(P2,A2:A20,0),MATCH(P3,B1:M1,0)).
Returns the intersection without hardcoding row or column numbers.

INDEX MATCH vs XLOOKUP performance notes

For very large ranges, prefer bounded tables (Excel Tables) over full-column references.
XLOOKUP is usually easier to read for one-dimensional lookups in new Microsoft 365 workbooks.
Keep INDEX MATCH when maintaining files that cannot use XLOOKUP yet.

Common errors

#REF! when row/column exceeds range
#VALUE! with invalid arguments

Use cases

Dynamic range references
Two-way lookups
Array operations

Frequently asked questions

What is the INDEX function in Excel? INDEX returns a value from a specific position in a range or array. The syntax is =INDEX(array, row_num, [column_num]). For example, =INDEX(A1:C10, 5, 2) returns the value from row 5, column 2 of the range. INDEX is powerful alone but becomes essential when combined with MATCH for flexible lookups that can search in any direction.
How do I use INDEX and MATCH together? INDEX/MATCH is a powerful lookup combination. MATCH finds the position, INDEX returns the value. Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Example: =INDEX(B:B, MATCH("Apple", A:A, 0)) finds "Apple" in column A and returns the corresponding value from column B. This works in any direction, unlike VLOOKUP.
What is the difference between INDEX/MATCH and VLOOKUP? INDEX/MATCH advantages over VLOOKUP: 1) Can look left (VLOOKUP only looks right); 2) Faster on large datasets; 3) Won't break if columns are inserted/deleted; 4) More flexible for two-way lookups; 5) Can return values from any column. VLOOKUP is simpler for basic right-lookups. XLOOKUP (Excel 365) combines the best of both.
How do I do a two-way lookup with INDEX/MATCH? For two-way lookups (finding a value at the intersection of a row and column), use two MATCH functions inside INDEX: =INDEX(B2:E10, MATCH(row_value, A2:A10, 0), MATCH(col_value, B1:E1, 0)). This finds the row position in the first MATCH and the column position in the second MATCH, then INDEX returns the intersecting value.
Why is INDEX/MATCH returning #N/A or #REF error? #N/A means MATCH couldn't find the value - check for typos, extra spaces (use TRIM), or data type mismatches. #REF! means the row/column number exceeds the array size. Ensure your MATCH range and INDEX range have the same number of rows/columns. Use 0 as the third MATCH argument for exact matches.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.