MATCH Function in Excel and Google Sheets
Purpose
Find the position of a value within a range and return its row or column number
Return value
The position number of lookup_value within lookup_array, or #N/A if not found
MATCH is the function that makes INDEX MATCH possible — and INDEX MATCH is the lookup pattern I reach for whenever VLOOKUP runs out of flexibility. On its own, MATCH returns the position of a value in a list: the second item returns 2, the fifth item returns 5. That position number is exactly what INDEX needs to pull the corresponding value from any other column, including columns to the left of the lookup column which VLOOKUP cannot reach. I also use MATCH standalone to check whether a value exists in a list at all — if it returns a number, the value is there; if it returns #N/A, it is not. MATCH handles both vertical column searches and horizontal row searches with the same syntax, making it useful for two-way table lookups where you need to locate both a row and a column position before retrieving the intersecting value.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Required | The value to search for. Can be a cell reference, text string, number, or the result of another formula. Data type must match the lookup_array — text does not match numbers. |
| lookup_array | Required | A single column or single row to search. Must be one-dimensional — a multi-column range causes a #N/A error. |
| match_type | Optional | 0 for exact match on unsorted data (use this almost always). 1 for largest value <= lookup_value on ascending sorted data. -1 for smallest value >= lookup_value on descending sorted data. Omitting this argument defaults to 1 which gives wrong results on unsorted data. |
How it works
MATCH scans the lookup_array from top to bottom (for columns) or left to right (for rows) and returns the position number of the first cell that matches the lookup_value. Position 1 is the first cell in the range, regardless of which row or column that cell is actually in on the sheet. With match_type 0, matching is exact and case-insensitive. Always specify 0 explicitly — omitting match_type defaults to 1 (approximate match on sorted data) which returns silently wrong results on unsorted lists. MATCH returns #N/A when no match is found — wrap it in IFNA for a clean not-found message. The most important use of MATCH is inside INDEX: INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) is the INDEX MATCH pattern that replaces VLOOKUP with no column-count restriction and no left-column requirement. For two-way lookups, use MATCH twice — once to find the row position and once to find the column position — then pass both results to a single INDEX.
Examples
1
Find the position of a product code in a product list to check whether it exists and where it sits.
| A | B | C | |
|---|---|---|---|
| 1 | Lookup | Position | Product Codes |
| 2 | PRD-004 | =MATCH(A2,D2:D11,0) | PRD-001 |
| 3 | PRD-099 | #N/A | PRD-002 |
| 4 | PRD-003 | ||
| 5 | PRD-004 | ||
| 6 | PRD-005 | ||
| 7 | PRD-006 | ||
| 8 | PRD-007 | ||
| 9 | PRD-008 | ||
| 10 | PRD-009 | ||
| 11 | PRD-010 |
Row 2: 4
— PRD-004 is the fourth item in the product code list so MATCH returns 4. PRD-099 does not appear in the list so MATCH returns #N/A.
The 0 as the third argument specifies exact match. Omitting it defaults to 1 (approximate match for sorted data) which would return wrong results here. Always write 0 explicitly for exact lookups on unsorted lists.
2
Use MATCH inside INDEX to look up an employee's department from an HR table — the INDEX MATCH pattern.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup ID | Department | Emp ID | Department |
| 2 | EMP-003 | =INDEX(E2:E7,MATCH(A2,D2:D7,0)) | EMP-001 | Engineering |
| 3 | EMP-005 | HR | EMP-002 | Marketing |
| 4 | EMP-003 | Sales | ||
| 5 | EMP-004 | Finance | ||
| 6 | EMP-005 | HR | ||
| 7 | EMP-006 | Legal |
Row 2: Sales
— MATCH finds EMP-003 at position 3 in D2:D7. INDEX returns the third value from E2:E7, which is Sales.
INDEX MATCH is the most important pattern built on MATCH. Unlike VLOOKUP, the return column (E) can be anywhere — left or right of the lookup column (D). Inserting new columns between D and E never breaks this formula.
3
Use MATCH to find the column position of a month name in a header row for a horizontal lookup.
| A | B | C | D | E | F | ||
|---|---|---|---|---|---|---|---|
| 1 | Jan | Feb | Mar | Apr | May | Jun | |
| 2 | Sales | 4200 | 3800 | 5100 | 4700 | 5500 | 4900 |
| 3 | Mar position | =MATCH("Mar",B1:G1,0) | 3 |
Row 2: 3
— Mar is the third item in the header range B1:G1 so MATCH returns 3. This position can then be passed to INDEX as the column_num argument to retrieve the March sales figure from the data row.
MATCH works identically on rows and columns — just supply a single-row range as lookup_array. For a two-way lookup, use one MATCH for the row position and another for the column position: =INDEX(data_range, MATCH(row_value,...), MATCH(col_value,...)).
Common use cases
1. Check whether a value exists in a reference list and find its position for downstream use
=MATCH(A2,D2:D100,0)
2. Use inside INDEX to look up values from any column regardless of position relative to the lookup column
=INDEX(E2:E100,MATCH(A2,D2:D100,0))
3. Find the column position of a header name for a two-way table lookup
=MATCH("Q3",A1:D1,0)
4. Combine two MATCH calls inside INDEX for a full two-way lookup by row and column
=INDEX(B2:G7,MATCH(A10,A2:A7,0),MATCH(B10,B1:G1,0))
5. Wrap in IFNA to return a friendly message when the lookup value is not in the list
=IFNA(MATCH(A2,D2:D100,0),"Not found")
Common errors
#N/A error
The lookup_value does not exist in the lookup_array, or there is a data type mismatch — the lookup value is a number but the list contains text versions of the same number, or vice versa.
Fix: Check with ISNUMBER on both the lookup value and the list. Wrap in IFNA for a clean not-found message.
Returns wrong position
match_type was omitted (defaults to 1) or set to 1 on unsorted data. Approximate match on an unsorted list returns an incorrect position without any error.
Fix: Always specify 0 as the third argument for exact match on unsorted data: =MATCH(A2,D2:D100,0).
#VALUE! error
The lookup_array is a multi-column range rather than a single column or row. MATCH requires a one-dimensional range.
Fix: Change the lookup_array to a single column like D2:D100 or a single row like A1:Z1. Never pass a full table range to MATCH.
Tips and variations
Always specify 0 as the third argument
Omitting match_type defaults to 1 (approximate match on sorted ascending data) which silently returns wrong results on unsorted lists. Writing 0 explicitly is a habit that prevents one of the most common MATCH mistakes I see in shared workbooks.
=MATCH(A2,D2:D100,0)
Wrap in IFNA for clean not-found handling
MATCH returns #N/A when the value is not found. IFNA intercepts only that error and replaces it with a message, while letting genuine formula errors like wrong range size remain visible — a tighter alternative to IFERROR for lookup functions.
=IFNA(MATCH(A2,D2:D100,0),"Not found")
Use INDEX MATCH instead of VLOOKUP for flexible lookups
Pair MATCH with INDEX and you get a lookup that works left, right, and with no column number to maintain. =INDEX(return_col,MATCH(lookup_value,lookup_col,0)) is the pattern I default to for any lookup more complex than a simple right-only table.
=INDEX(E2:E100,MATCH(A2,D2:D100,0))
Excel vs Google Sheets
Excel vs Google Sheets
MATCH works identically in Excel 2003 and newer and every version of Google Sheets. The syntax, match_type behaviour, and #N/A return on no-match are the same in both applications. Formulas copy between them without changes.
Related reading
Frequently asked questions
MATCH searches a range for a value and returns the position number of that value within the range — not the value itself, but where it sits. For example, if Alice is the third item in a list, MATCH returns 3. This position number is most commonly used as the row or column argument inside INDEX to build the INDEX MATCH lookup pattern.
VLOOKUP finds a value and returns a result from a specified column — it does the find and the return in one step. MATCH only finds the position. INDEX then uses that position to return the value. The INDEX MATCH combination is more flexible than VLOOKUP because the return column can be to the left of the lookup column, the lookup column does not have to be first, and inserting columns never breaks the formula.
match_type controls how MATCH searches. 0 means exact match — MATCH returns the position only if an identical value is found. 1 means find the largest value less than or equal to the lookup value, requiring the range to be sorted ascending. -1 means find the smallest value greater than or equal to the lookup value, requiring the range to be sorted descending. Always use 0 for exact match lookups on unsorted data.
MATCH returns #N/A when the lookup value does not exist in the lookup array. Wrap MATCH in IFNA or IFERROR to replace that error with a friendly message: =IFNA(MATCH(A2,B:B,0),"Not found"). The #N/A also propagates through any INDEX formula that uses the MATCH result.
Yes. MATCH works on both columns and rows — just supply a single-row range as the lookup_array. =MATCH("Mar",A1:L1,0) finds the position of Mar in a header row, which you can then use in INDEX to retrieve data from that column across multiple rows.