How to find the row number of a value in Excel

MATCH gives the position in the range, not the row

The sheet is an order log with a title in row 1 and headers in row 3, so the first order sits in row 4. G1 holds the customer to find. MATCH(lookup_value, range, 0) searches the range and returns where the first match is, counted from the top of that range.

That number is the position, and it is exactly what INDEX wants, which is why INDEX and MATCH work together. It is not the row number you see on the left of the sheet. Harbor Foods is the 1st item in B4:B11 but lives in row 4. The two only agree when your range starts in row 1.

Always type the 0 at the end. Without it, MATCH uses approximate match, which assumes the column is sorted A to Z and can return a wrong position with no error at all.

Turn the position into the sheet row

Add the row the range starts on, minus 1. Write it as ROW(B4)-1 instead of typing 3, so the formula still works if someone inserts a row above the table and the data moves to row 5: the reference shifts and ROW follows it.

Another way is to match against the whole column, =MATCH(G1,B:B,0), because then position and row are the same thing. It returns 4 here too. The catch is that it also searches the title and header rows, so if the value you look for also appears above the table, as a header or a note, you get that row instead.

No offset to maintain: ROW of INDEX

INDEX usually looks like it returns a value, but it actually returns a reference to a cell. ROW can read the row number of that reference. So INDEX(B4:B11,MATCH(G1,B4:B11,0)) points at the matching cell, B4, and ROW turns it into 4, with no "+ROW(B4)-1" to get right.

It is the version to use when the range is a named range or a table column whose start you do not want to hard-code. It works in every version of Excel. Swap ROW for COLUMN to get the column number of a match in a header row, as in =COLUMN(INDEX(A3:D3,MATCH("Amount",A3:D3,0))), which returns 4 for column D.

XMATCH, and the last occurrence (Excel 365 and 2021)

XMATCH is the modern MATCH. Its default is an exact match, so forgetting the third argument cannot silently give a wrong answer, and its fourth argument sets the search direction. With -1 it searches from the bottom up, which gives the last occurrence. That is the most recent order when the log is in date order.

The result is still a position: Harbor Foods' last order is the 8th entry. Add the same ROW(B4)-1 offset to get row 11.

In Excel 2019 and earlier there is no XMATCH. =SUMPRODUCT(MAX((B4:B11=G1)*ROW(B4:B11))) returns 11 as well: the comparison is 1 on matching rows and 0 elsewhere, so multiplying by ROW keeps only the matching row numbers and MAX picks the largest. It returns 0 rather than #N/A when nothing matches, so test for 0 before you use it.

Every row number with FILTER and ROW

ROW(B4:B11) returns the row numbers 4 to 11 as a list. FILTER keeps the ones where the customer matches, so you get every matching row, already as sheet rows, with no offset to add. The result spills down, one row number per cell. Leave empty cells below it or you get #SPILL!.

Look at the result: 4, 9 and 11, but not 7. Row 7 holds "Harbor Foods " with a trailing space, so it does not equal G1. This is the reason most "it's right there but Excel can't find it" cases happen. Compare against TRIM(B4:B11) instead and row 7 is included: 4, 7, 9 and 11. Better still, clean the column once with TRIM so every formula agrees.

To show the list in one cell, wrap it: =TEXTJOIN(", ",TRUE,FILTER(ROW(B4:B11),B4:B11=G1)) gives "4, 9, 11". For the 2nd match only, use =INDEX(FILTER(ROW(B4:B11),B4:B11=G1),2), which returns 9.

Handle #N/A when the value is not there

MATCH and XMATCH return #N/A when nothing matches, and the error passes through the +ROW(B4)-1 part. For a value that is genuinely missing, catch it with IFNA and show a message. IFNA only catches #N/A, so a typo in the formula that causes #VALUE! or #REF! still shows up. IFERROR would hide those too.

FILTER has its own third argument for this: =FILTER(ROW(B4:B11),B4:B11="Acme","None") shows None instead of #CALC!.

Before you blame the data, check the usual causes: a trailing space like row 7, a number stored as text in one place and as a number in the other, or a lookup cell that contains something slightly different. Matching is not case-sensitive, so "harbor foods" finds Harbor Foods; for a case-sensitive row, use =XMATCH(TRUE,EXACT(B4:B11,G1))+ROW(B4)-1.

Which method to pick

To feed INDEX, use the plain MATCH position with no offset. To report the sheet row of the first match, MATCH(...,0)+ROW(first cell)-1 works in every version. For the last match in Excel 365 or 2021, XMATCH with -1; in older versions, the SUMPRODUCT and MAX formula. For every match, FILTER(ROW()). If you only need the value from that row, skip the row number and use XLOOKUP or INDEX and MATCH directly.