INDEX and MATCH functions in Google Sheets

INDEX returns the cell at a position in a range and MATCH finds the position of a value; together they make a flexible lookup.

MATCH tells you where a value is. INDEX tells you what is at a place. Joined, they form a lookup that can search any column and return from any other, and that works in every spreadsheet program.

Syntax

=INDEX(reference, [row], [column])

=MATCH(search_key, range, [search_type])

Argument	What it is
`reference`	The range to take a value from.
`row, column`	The position inside the range, counting from 1.
`search_key`	For MATCH: the value to find.
`range`	For MATCH: one row or one column to search.
`search_type`	0 for an exact match. 1 (the default) for the largest value not above the key in a range sorted upwards. -1 for a range sorted downwards.

Examples

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=MATCH("C301",A2:A8,0)
Result: 5
C301 is the fifth code in the list.
=INDEX(B2:B8,5)
Result: LED Bulb
The fifth item name.
=INDEX(B2:B8,MATCH("C301",A2:A8,0))
Result: LED Bulb
The two together: find the row, return the name on it.
=INDEX(A2:A8,MATCH("Steel Bottle",B2:B8,0))
Result: D401
A lookup to the left.
=INDEX(A2:E8,MATCH("D401",A2:A8,0),MATCH("Stock",A1:E1,0))
Result: 55
A two-way lookup: one MATCH for the row, one for the column.
=INDEX(D2:D8,COUNTA(D2:D8))
Result: 450
The last value in a column.

Step by step

=MATCH("C301", A2:A8, 0) returns 5: the code is the fifth entry.
=INDEX(B2:B8, 5) returns the fifth name.
Put the first inside the second: =INDEX(B2:B8, MATCH("C301", A2:A8, 0)).

The pattern to remember is =INDEX(answer_column, MATCH(key, search_column, 0)).

Always type the 0 in MATCH. Its default assumes sorted data and returns a nearby position on unsorted data, without warning.

Why use it

The answer column may be to the left of the search column.
Inserting columns does not break it, because it refers to the columns themselves.
A second MATCH picks the column by its heading, giving a two-way lookup, as in the fifth example.
It behaves the same in Sheets and in every version of Excel, so it is the safest choice for a file that will be downloaded and passed around.

INDEX with array functions

In Sheets, INDEX has a second life: taking one piece from a result that spills.

=INDEX(SPLIT(A2, " "), 1, 2) is the second word of a cell.
=INDEX(SORT(A2:C20, 3, FALSE), 1, 1) is the top entry of a sorted list.
=INDEX(GOOGLEFINANCE("CURRENCY:USDINR","price",DATE(2026,1,15)), 2, 2) picks the figure out of the small table that a historical request returns.

Leave the row empty to get a whole column, or the column empty to get a whole row: =INDEX(A2:E8, , 4) is the entire fourth column.

A lesser-known point: a bare INDEX around a formula makes it behave like ARRAYFORMULA, so =INDEX(C2:C9*2) spills too. Many Sheets users type INDEX as a shorter wrapper.

MATCH on its own

Is a value in a list? =ISNUMBER(MATCH(A2, List!A:A, 0)).
Which column has this heading? =MATCH("Price", A1:E1, 0), to feed VLOOKUP's index so that it survives column moves.
Wildcards: with search type 0, * and ? work: =MATCH("*Bottle*", B2:B8, 0).

Not for self-filling columns

INDEX(..., MATCH(...)) does not expand down a column inside ARRAYFORMULA in the way people expect: INDEX returns a single cell. For a column that fills itself, use VLOOKUP in ARRAYFORMULA, or XLOOKUP.

Common mistakes

The two ranges starting on different rows, so every answer is off by one.
Forgetting the 0 in MATCH.
A MATCH range of several columns. It must be one row or one column.
#N/A from MATCH when the value is absent, text against number, or stray spaces.

In Excel

Both functions are identical in Excel. Excel names MATCH's last argument match_type.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.