MATCH function in Excel

MATCH returns the position of a value in a row or column.

MATCH does not return a value from your data. It returns a position: "the thing you asked for is the 4th item in this list". That number is rarely the final answer. It is what other functions need, above all INDEX.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Argument	What it is
`lookup_value`	The value to look for.
`lookup_array`	A single row or a single column to search.
`match_type`	0 for an exact match. 1 (the default) for the largest value not more than the lookup value, in a range sorted upwards. -1 for the smallest value not less, in a range sorted downwards.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

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("B202",A2:A8,0)
Result: 4
B202 is the 4th code in the list, so the answer is 4. It is a position, not a row number of the sheet.
=MATCH("Price",A1:E1,0)
Result: 4
MATCH works along a row too: Price is the 4th heading.
=MATCH("Z999",A2:A8,0)
Result: #N/A
Not in the list: #N/A.
=ISNUMBER(MATCH("C301",A2:A8,0))
Result: TRUE
A common test for "is this value in the list?". TRUE if it is found.
=MATCH("*Bottle*",B2:B8,0)
Result: 7
With match type 0 you may use wildcards: the position of the first item containing Bottle.

Sample sheet: A marks table and a grade table. The grade table lists the lowest mark of each grade, in rising order.

	A	B	D	E
1	Name	Marks	From	Grade
2	Asha	91	0	E
3	Ravi	45	33	D
4	Meena	79	50	C
5	Kabir	28	65	B
6	Tara	65	80	A

=MATCH(79,D2:D6,1)
Result: 4
Match type 1 on a sorted list: 79 falls in the 4th band, the one that starts at 65.

How it works

MATCH goes through a single row or column and reports where the lookup value sits, counting from the start of the range. If the range starts at A2, the first cell is position 1, even though it is in row 2 of the sheet.

The match type

match_type	What it finds	The range must be
0	the first exact match	in any order
1 or left out	the largest value less than or equal to the lookup value	sorted smallest to largest
-1	the smallest value greater than or equal to the lookup value	sorted largest to smallest

Type the 0. The default is 1, an approximate match, and on an unsorted list it returns a wrong position without warning. This is the same trap as VLOOKUP's last argument.

What MATCH is used for

With INDEX, to build a lookup that can look in any direction. See the examples on the INDEX page.
To find a column by its heading. =VLOOKUP(code, table, MATCH("Price", headings, 0), FALSE) keeps working when columns are moved, because the column number is worked out, not typed.
To check whether a value is in a list. ISNUMBER(MATCH(...)) gives TRUE or FALSE. It is the usual way to compare two lists: put it beside the first list and it flags which entries appear in the second.
To rank a value in bands, with match type 1, as in the last example.

Wildcards

With match type 0, a question mark stands for any one character and a star for any run of characters. "A*" finds the first entry that starts with A. To search for a real star or question mark, put a tilde in front: "~*".

Common mistakes

#N/A although the value is visible. The usual causes are a number stored as text, or spaces around the value. See VALUE and TRIM.
Selecting a block of several rows and columns. MATCH needs one row or one column.
Reading the result as a row number. It is a position inside the range. Add the rows above the range if you need the sheet's row number.

MATCH ignores the difference between capital and small letters.

In Google Sheets

MATCH is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

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