Finding the Row Number after a VLOOKUP (Excel 2000)

Topic

New Reply

(

 and 

tagsinseted by HansV to preserve spaces in table – see Help 19)

Is there a way to write a formula to find the address, or row number, of the cell which is the result of a VLOOKUP formula:
=vlookup(20,A2:B5,2). Now I would like a way to know that this result (Roger) was found on Row 3. Any help would be appreciated
Thanks — Cindy

        A        B
1       AGE      NAME
2       10       Cindy
3       20       Roger
4       30       Bill

Reply | Quote

Replies

You can use MATCH instead of VLOOKUP for this:

=MATCH(20,A2:A4,0)

will return 2 to indicate that the search value was found on row 2 of the range A2:A4. This is a relative row number within the searched range. To convert to an absolute row number, you must add the row number of the first row in the searched range, minus 1:

=MATCH(20,A2:A4,0)+ROW(A2:A4)-1

Reply | Quote

I would just do something simple like expanding the table to add a column of row numbers. In your case C2 would contain =ROW(C2), C3 contains =ROW(C3), etc. Then your formula would be =VLOOKUP(20,A2:C4,3). HTH –Sam

Reply | Quote