MATCH Function for Exact Matches: How-To

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to use the MATCH function in Excel and Google Sheets to find the position of an exact value in a row or column. You’ll learn the syntax, see a step-by-step example, and avoid common pitfalls.

What You’ll Need

A spreadsheet with a list of values (e.g., names, IDs, or codes).
A specific value you want to locate within that list.
Basic familiarity with entering formulas in a cell.

Step-by-Step

Identify your lookup value. This is the exact text or number you’re searching for. For example, you might be looking for the product ID “A-100”.
Select the range to search. This should be a single row or a single column, not a multi-dimensional array. For instance, if your IDs are in cells A2:A100, your range is A2:A100.
Decide on match type. For an exact match, you’ll use 0 as the third argument. This tells the function to find the first value that exactly equals your lookup value.
Enter the formula. In a blank cell, type: =MATCH("A-100", A2:A100, 0). Replace the quoted text with your actual lookup value (or a cell reference containing it, like D1).
Press Enter. The cell will display the relative position of the match within the range. For example, if “A-100” is in cell A10, the result is 9 (since A2 is position 1, A3 is 2, and so on).
Use the result. You can use this number in other formulas, like INDEX, to return the value from a different column in the same row.

Common Mistakes

Forgetting the third argument. If you omit 0, MATCH defaults to approximate match (1), which requires the range to be sorted and may return a wrong position.
Using a multi-column range. MATCH only works with a single row or column. If you select A2:C100, you’ll get a #N/A error.
Mismatched data types. Searching for a number when your list contains text (or vice versa) will fail. Ensure both are the same type (e.g., both numbers or both text strings).
Leading/trailing spaces. Extra spaces in the lookup value or in the list entries will prevent an exact match. Use TRIM() to clean data.

Tips

Use a cell reference for the lookup value (e.g., =MATCH(D1, A2:A100, 0)) so you can change the value without editing the formula.
For case-insensitive matching, MATCH is case-insensitive by default in both Excel and Google Sheets. If you need case-sensitive, use EXACT with an array formula.
When you expect the value to be present, wrap MATCH in IFERROR to display a friendly message like “Not found” instead of #N/A.
If your list is sorted, you can use 1 or -1 for approximate matches, but for exact matches always use 0.

Troubleshooting

If your MATCH formula returns an error, here’s what to check:

#N/A – The lookup value was not found. Double-check the spelling, case, and any hidden spaces. Also confirm the range is correct.
#VALUE! – The range is not a single row or column, or the match type is invalid (not 0, 1, or -1).
#NAME? – You may have typed the function name incorrectly, or you’re using a non-English locale where the function is called something like EQUIV.
Unexpected position – If you forgot the 0, MATCH may return a position for an approximate match. Re-check your formula.

Frequently Asked Questions

What does MATCH return?

It returns the relative position of the first exact match in the range. For example, if the match is the 5th cell in the range, it returns 5.

Can MATCH search for text that contains wildcards?

Yes, when using exact match (0), you can use wildcards like * (any sequence) and ? (single character) in the lookup value if it is text. For example, =MATCH("A*", A2:A100, 0) finds the first cell starting with “A”.

Does MATCH work with numbers and text?

Yes, but the lookup value must be the same data type as the entries. If your list contains numbers stored as text, you’ll need to convert them or use TEXT in the lookup.

How do I find the second occurrence?

MATCH only returns the first occurrence. To find subsequent ones, you can use an array formula or a helper column with COUNTIF, but it’s more complex.

Is MATCH case-sensitive?

No, by default it is not. To make it case-sensitive, combine it with EXACT in an array formula like =MATCH(TRUE, EXACT(A2:A100, D1), 0) (entered with Ctrl+Shift+Enter in Excel).

What is the difference between MATCH and VLOOKUP?

VLOOKUP returns a value from a different column, while MATCH returns the position number. They are often used together: INDEX returns the value, MATCH provides the row number.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.