Excel Formula: Second Occurance in a vlookup

the vlookup function in microsoft excel is very useful. however, it only returns the first occurance in a range. here i have written a complex formula that will extract the "n"th occurance.

it's easy if you can use named ranges to define the range. defining only the first column is enough as the range for the vlookup is specified through an offset.


=VLOOKUP(J5,OFFSET($A$1,MATCH(J5,$C$2:$C$200,0)+(K5-1),M5,COUNTIF($C$2:$C$200,J5),N5),2+(L5-2),FALSE)

where:
J5 - Lookup Value
K5 - Required Occurance
L5 - Required Column
M5 - Start Column Number in Range for vlookup (Offset from $A$1)
N5 - Number of Columns in Range for vlookup
$C$2:$C$200 - Lookup Range (This need not be the full range, only the column where the lookup value can be found is enough)

note: this is a vector formula; so you should use CTRL+SHIFT+ENTER to get it working, if not it will return an error.


i wrote this for one of my projects and may be i havent explained the process clearly; so if you find an issue, or if it is not working, post a note or email me. i can explain how to make it work :-)