The MATCH Function in Excel

The Excel MATCH function tells you where a value is in a list: its position as a number, such as 4 for the fourth item. On its own that sounds modest, but MATCH is the engine behind INDEX MATCH, flexible VLOOKUPs and "is this value in my list?" checks. This guide is for beginners and covers the syntax, the three match types, and the most useful ways to combine MATCH with other functions.

What does MATCH do in Excel?#

MATCH searches a single row or column for a value and returns its position in that range.

=MATCH(lookup_value, lookup_array, [match_type])

MATCH works in every version of Excel, on Windows, Mac and the web. If you need the value rather than its position, MATCH is half of the answer; INDEX is the other half.

How to use MATCH: find a position step by step#

Here is a small employee list:

F2 holds Marco. In G2, type:

=MATCH(F2,B2:B7,0)

The result is 4, because Marco is the 4th cell of B2:B7. Notice it's not 5, his row number on the sheet: MATCH counts from the first cell of the range you give it. If you select B1:B7 instead, the answer becomes 5, which can throw off any INDEX that uses it. Keep the MATCH range and the INDEX range starting on the same row.

MATCH also works across a row: =MATCH("Salary",A1:D1,0) returns 4, the column position of the Salary header.

Match types: 0, 1 and -1 explained#

The third argument decides how MATCH compares values. Use 0 unless you have a clear reason not to.

Match type 1 is useful for bands and tiers. With commission thresholds 0, 10,000, 25,000 and 50,000 in A11:A14, =MATCH(30000,A11:A14,1) returns 3: 25,000 is the largest threshold that 30,000 reaches. Then =INDEX(B11:B14,MATCH(30000,A11:A14,1)) returns that tier's rate.

The danger: if you leave out the third argument, Excel uses 1. On unsorted names, that can return a wrong position with no error. Our comparison of MATCH type 0 vs 1 vs -1 goes through each case in detail.

How to use MATCH with INDEX#

INDEX returns the value at a position, and MATCH finds the position, so together they make a lookup.

=INDEX(A2:A7,MATCH(F2,B2:B7,0))

MATCH finds Marco at position 4, and INDEX returns the 4th ID: E-204. Unlike VLOOKUP, the return column (A) is to the left of the search column (B), which is why INDEX MATCH is the classic answer to looking up to the left. The INDEX MATCH guide covers it step by step.

Use two MATCH functions for a two-way lookup, one for the row and one for the column:

=INDEX(A2:D7,MATCH(F2,B2:B7,0),MATCH("Department",A1:D1,0))

This returns Sales, Marco's department. Change "Department" to "Salary" and you get 49,000, without editing anything else.

How to make VLOOKUP's column number automatic with MATCH#

VLOOKUP breaks when someone inserts a column, because its column number is typed in. MATCH can calculate it from the header instead:

=VLOOKUP(F2,B1:D7,MATCH("Salary",B1:D1,0),FALSE)

MATCH finds "Salary" in position 3 of B1:D1, which is exactly the column number VLOOKUP needs for the table B1:D7. The result is 49000. If a column is inserted, the header moves and MATCH updates the number automatically. Make sure the header range starts in the same column as the table.

How to check if a value exists in a list#

MATCH returns #N/A when it can't find the value. Wrap it in ISNUMBER to turn that into TRUE or FALSE:

=ISNUMBER(MATCH("Zoe",B2:B7,0))

This returns FALSE, because Zoe isn't on the list. Inside IF it becomes a label: =IF(ISNUMBER(MATCH(F2,B2:B7,0)),"Yes","No") returns Yes for Marco. This pattern is a common way to compare two lists, for example to flag which applicants are already employees.

MATCH with wildcards and case#

With match type 0, MATCH accepts the wildcards * (any characters) and ? (one character). =MATCH("Ma*",B2:B7,0) returns 4, the first name starting with "Ma". MATCH is not case-sensitive, so =MATCH("marco",B2:B7,0) also returns 4.

For a case-sensitive match, combine it with EXACT: =MATCH(TRUE,EXACT(B2:B7,F2),0). In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Why does MATCH return #N/A?#

#N/A means MATCH didn't find the value. Check these in order:

1. Typos or extra spaces: "Marco " with a trailing space won't match. Use TRIM to clean the data.
2. Numbers stored as text: the number 1001 doesn't match the text "1001".
3. The range misses the value: the list grew but the range didn't.
4. Wrong match type: with 1 or -1 on unsorted data, MATCH can return #N/A or a wrong position.

To show a friendly message, wrap it: =IFERROR(MATCH(F2,B2:B7,0),"Not found").

In Excel 2021 and Microsoft 365, XMATCH defaults to exact match and can search from the bottom; see MATCH vs XMATCH. MATCH stays the safer choice for files that must open in older versions. MATCH also does the heavy lifting in lookups with multiple criteria, a favourite of Excel interview questions. The MATCH function guide has the full reference.

Practise it#

Try the live exercise on this page to find Marco's position. Then practise INDEX and MATCH in a live spreadsheet, where you'll use MATCH on its own, put it inside INDEX, and finish with a two-way lookup.