Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding the Row Number after a VLOOKUP (Excel 2000)

    (<!t>[pre]<!/t> and <!t>[/pre]<!/t> tagsinseted by HansV to preserve spaces in table - see <!help=19>Help 19<!/help>)

    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
    <pre> A B
    1 AGE NAME
    2 10 Cindy
    3 20 Roger
    4 30 Bill
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding the Row Number after a VLOOKUP (Excel 2000)

    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

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the Row Number after a VLOOKUP (Excel 2000)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •