Results 1 to 3 of 3
  1. #1

    Vlookup on second find

    In Excel2000, is there a way to use a vlookup that finds a match off of the second find. For example, I have 2 cells that contain East Total. I want to the vlookup to find the second East Total and give me results.


  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Thanked 0 Times in 0 Posts

    Re: Vlookup on second find

    If your list with "East Total" is in column A, this will return the corresponding cell in column B for the second value of "East Total":

    =INDEX(B1:B18,SMALL(IF(A1:A18="East Total",ROW(A1:A18),""),2),1)

    This is an array function and must be entered using Control+Shift+Enter

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    NJ, USA
    Thanked 1 Time in 1 Post

    Re: Vlookup on second find

    No guarantees, but I think the following does what you need.
    Assume that cell A1 contains the value that you're trying to find (East Total in your example), with the VLOOKUP formula next to it in cell B1. Assume the database has just two columns and is in cells A4:B8. You're looking to obtain the value in column B corresponding to the second occurrence of East Total, say, in range A4:A8.
    In cell B1, enter the following formula
    <Pause for a deep breath>
    =VLOOKUP(A1,OFFSET(A4:B8,MATCH(A1,A4:A8,0),,,ROWS( A4:A8)-MATCH(A1,A4:A8,0)),2,FALSE)
    Let me know if you want an explanation!
    P.S. for some reason, it doesn't seem to work if the first and second occurrence of East Total are in the last two rows of the database. Not sure why.

Posting Permissions

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