Quick answer
To find the row number of a matching value in Excel, use =MATCH(F2,A:A,0). Because the range starts at row 1, the position MATCH returns is the worksheet row number. If your range starts lower down — A2:A100, say — MATCH returns the position within that range, so add the offset: =MATCH(F2,A2:A100,0)+ROW(A2)-1. The final 0 is not optional; leave it out and Excel returns the wrong row without warning.
Nearly every problem with this formula comes from one misunderstanding, so it is worth being blunt about it up front: MATCH does not return a row number. It returns a position within whatever range you handed it. Those two things happen to be identical when the range begins at row 1, which is why the formula seems to work until the day someone uses A2:A100 and every answer is one too small.
The two forms, and why they differ
Suppose column A holds product codes from A1 down, and F2 holds the code you are looking for.
=MATCH(F2,A:A,0)If the code sits in A57, this returns 57. The range starts at row 1, so position 57 and row 57 are the same thing.
=MATCH(F2,A2:A100,0)Same data, same code in A57, and this returns 56. Not a bug: A57 is the 56th cell of a range that starts at A2. To turn a position into a row number, add back where the range began:
=MATCH(F2,A2:A100,0)+ROW(A2)-1ROW(A2) is 2, so the result is 56 + 2 − 1 = 57. Writing ROW(A2) rather than typing 2 means the formula survives someone inserting a row above your data — the reference moves, the arithmetic follows.
Never omit the third argument
The third argument of MATCH is match_type, and this is the trap that produces confidently wrong answers.
| match_type | What it finds | Requires |
|---|---|---|
0 | The first exact match | Nothing — data can be in any order |
1 or omitted | The largest value less than or equal to the lookup value | Data sorted ascending |
-1 | The smallest value greater than or equal to the lookup value | Data sorted descending |
On unsorted data, match_type 1 does not error. It returns a row — just not the right one. That silence is what makes it dangerous, and it is the reason experienced users type the ,0) automatically.
The last match instead of the first
MATCH stops at the first hit. When a code appears several times and you want the most recent entry, you need to search from the bottom.
In Microsoft 365 and Excel 2021, XMATCH takes a search direction as its fourth argument, where -1 means last-to-first:
=XMATCH(F2,A2:A100,0,-1)+ROW(A2)-1In any version, this classic does the same job:
=LOOKUP(2,1/(A2:A100=F2),ROW(A2:A100))It reads as nonsense until you take it apart. (A2:A100=F2) gives an array of TRUE and FALSE. Dividing 1 by that array turns every TRUE into 1 and every FALSE into a #DIV/0! error. LOOKUP then hunts for the value 2 in an array whose largest real number is 1 — it never finds it, so it falls back on the last numeric value it saw, which is the last matching row. That returns a genuine worksheet row number directly, with no offset arithmetic.
Every formula in one place
| What you need | Formula | Version |
|---|---|---|
| Worksheet row of the first match | =MATCH(F2,A:A,0) | Any |
| Row of the first match, range starts lower down | =MATCH(F2,A2:A100,0)+ROW(A2)-1 | Any |
| Position within the range (not the row) | =MATCH(F2,A2:A100,0) | Any |
| Row of the last match | =LOOKUP(2,1/(A2:A100=F2),ROW(A2:A100)) | Any |
| Row of the last match | =XMATCH(F2,A2:A100,0,-1)+ROW(A2)-1 | 365 / 2021 |
| Row matching two conditions | =MATCH(1,(A2:A100=F2)*(B2:B100=G2),0)+ROW(A2)-1 | Any (see note) |
| Every matching row | =FILTER(ROW(A2:A100),A2:A100=F2) | 365 / 2021 |
| How many matches there are | =COUNTIF(A2:A100,F2) | Any |
| Row number of the cell holding the formula | =ROW() | Any |
| The cell address rather than the row | =ADDRESS(MATCH(F2,A:A,0),1) | Any |
The two-condition formula multiplies two TRUE/FALSE arrays, so 1 × 1 = 1 marks the only row where both hold. It is an array formula, which in Excel 2019 and earlier means Ctrl+Shift+Enter rather than plain Enter. There is more on this pattern, and on the alternatives, in lookup with multiple criteria.
You probably do not want the row number
This is worth saying because it saves a lot of fragile spreadsheets. Most people asking for a row number are on their way to fetching a value from that row. You do not need the number in between:
=INDEX(C:C,MATCH(F2,A:A,0))That returns the value from column C on the matching row, in one step. If you park the row number in a helper cell and then build a reference around it with INDIRECT, you get something that is slower, harder to audit and volatile — INDIRECT recalculates on every change anywhere in the workbook. VLOOKUP, INDEX/MATCH and XLOOKUP all skip the middle step.
Genuine reasons to want the number itself do exist: reporting where a duplicate or an error sits so a colleague can go and look at it, feeding a row index into VBA, or checking whether a value moved between two versions of a file.
Why it returns #N/A when the value is clearly there
Trailing spaces. Widget and Widget are different strings. Test with =COUNTIF(A2:A100,TRIM(F2)) — if that finds it and MATCH does not, spacing is your answer. Cleaning the source column is better than wrapping every formula in TRIM, and the first-and-last-word formulas cover the related job of splitting those values up.
Numbers stored as text. A lookup value of 2026 will never match a cell containing the text 2026, and both look identical. Text aligns left by default, numbers right — that is the quickest visual check.
The wrong match_type. Covered above, and worth re-checking first whenever a result is merely wrong rather than an error.
Whole columns on a big workbook. A:A is convenient and gives you row numbers for free, but Excel evaluates the used range each time. On a workbook with hundreds of thousands of rows and dozens of these formulas, switch to a bounded range or a Table reference and accept the offset arithmetic.
Frequently asked questions
Does MATCH return the row number in Excel?
Not directly. MATCH returns the position of a value within the range you gave it. That equals the worksheet row number only when the range starts at row 1, such as A:A. For a range like A2:A100, add the offset with +ROW(A2)-1.
How do I find the row number of a matching value?
Use =MATCH(F2,A:A,0), where F2 holds the value you are looking for. The third argument 0 forces an exact match and works on unsorted data.
How do I get the row number of the last match rather than the first?
Use =LOOKUP(2,1/(A2:A100=F2),ROW(A2:A100)), which works in every version and returns a real worksheet row number. In Microsoft 365 or Excel 2021 you can use =XMATCH(F2,A2:A100,0,-1) and add the range offset.
How do I return every row number that matches?
In Microsoft 365 or Excel 2021, =FILTER(ROW(A2:A100),A2:A100=F2) spills one row number per match. In older versions, =COUNTIF(A2:A100,F2) at least tells you how many there are.
Why does MATCH give me the wrong row?
The most common cause is a missing third argument. Without it, match_type defaults to 1, which looks for the largest value less than or equal to yours and assumes the data is sorted ascending. On unsorted data it returns a plausible but incorrect row rather than an error. Add ,0.
Sources
- MATCH function — Microsoft Support. Accessed September 10, 2026.
- XMATCH function — Microsoft Support. Accessed September 10, 2026.
- INDEX function — Microsoft Support. Accessed September 10, 2026.
- How to correct a #N/A error in INDEX/MATCH functions — Microsoft Support. Accessed September 10, 2026.
- Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support. Accessed September 10, 2026.
1 thought on “Find the Row Number of a Matching Value in Excel”