# Thread: Vlookup on second find

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.

Thanks

2. ## 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. ## 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
•