The MATCH function in Excel

Q: How do I use MATCH with INDEX?

Use MATCH to find the row number and pass it to INDEX: =INDEX(C2:C9, MATCH(F2, A2:A9, 0)). This returns the value from column C on the row where column A equals F2.

MATCH tells you where a value sits in a list: its position number, not the value itself. That position is exactly what INDEX needs, which is why the two are almost always used together. It's also useful by itself to check whether an item exists or to find which column holds a given header.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value	The value whose position you want.
lookup_array	A single row or single column to search.
match_type	Optional. `0` for an exact match (use this almost always). `1` (the default) finds the largest value less than or equal to the lookup in an ascending list. `-1` does the reverse for a descending list.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

The support queue is listed in the order tickets will be handled. In G2, use MATCH to find the queue position of the ticket in F2.

	A	B	C	F	G
1	Ticket	Customer	Priority	Ticket	Position
2	T-3311	Atlas Freight	Urgent	T-3315
3	T-3307	Fernhill Bakery	High
4	T-3309	Nimbus HR	High
5	T-3315	Corvid Games	Medium
6	T-3302	Bluebird Dental	Medium
7	T-3318	Quarry Outdoor	Low
8
9
10

Tip: Search the ticket column for F2, and ask for an exact match.

Examples

=MATCH("Pro",A2:A5,0)→ 3

Finds where Pro appears in the plan list, counting from the top of the range.

=MATCH("Q3",B1:E1,0)→ 3

MATCH works across a row too. This finds which column of the header row holds Q3.

=MATCH(1800,G2:G5,1)→ 3

With match type 1, MATCH returns the last threshold that is less than or equal to 1800. The list must be sorted smallest to largest.

Example data

1	Plan	Q1	Q2	Q3	Q4	Min spend	Tier
2	Basic	120	135	150	162	0	Bronze
3	Plus	88	97	110	131	250	Silver
4	Pro	64	70	81	95	1000	Gold
5	Team	21	30	38	44	5000	Platinum

Common mistakes

Leaving out the 0
Without a third argument MATCH assumes 1 (approximate) and can return a wrong position on unsorted data. Write ,0) for names, IDs and codes.
Expecting a value back
MATCH returns a number like 3, not the item in row 3. Wrap it in INDEX to get the value: INDEX(B2:B9, MATCH(...)).
Searching a two-dimensional range
lookup_array must be one row or one column. A block like A2:D9 returns #N/A. Point it at the single column you're searching.
INDEX and MATCH ranges don't line up
If MATCH searches A2:A9, the INDEX range must also start on row 2, like C2:C9. Otherwise every result is off by a row.

FAQ

What does the 0 mean in MATCH?+

It asks for an exact match. If the value isn't there you get #N/A instead of the position of a nearby item.

Is MATCH case-sensitive?+

No. MATCH treats "pro" and "Pro" as the same. If case matters, you'll need a different approach such as EXACT inside an array formula.

How do I use MATCH with INDEX?+

Use MATCH to find the row number and pass it to INDEX: =INDEX(C2:C9, MATCH(F2, A2:A9, 0)). This returns the value from column C on the row where column A equals F2.

Lesson

Master MATCH step by step: INDEX and MATCH

Combine INDEX and MATCH to look up in any direction, including returning a column to the left of the one you search.

Related functions

INDEX XLOOKUP VLOOKUP