Excel Alternative to VLOOKUP to get nth reference in array

When working in Excel you may find the need for an alternative to the VLOOKUP formula if you need to pull data from an array, but want to get not the first matching occurrence, but the 2nd, 3rd, 4th, and so on. Try using this formula:

=INDEX($K$2:$K$13,SMALL(IF($B$2=$J$2:$J$13,ROW($J$2:$J$13)-ROW($J$2)+1),1))

. . . where the value you want to look up is in B2, its match is in column J, and you want to return a value from column K. This is an array formula, so be sure to press CTRL + SHIFT + ENTER when putting it in a cell.

So in this example in columns A to C, we have metadata for several emails. We want to to see where there is an email with a matching date in another email production set listed in columns I to K. So the INDEX formula as entered in column D searches through the array and finds the first reference to the date in B2, 7/14/2001, and returns the subject 'Meeting Minutes'. To find the second subject used for a email sent on 7/14/2001, we simply change the number referenced at the end of the formula from 1 to 2 [. . +1),1)) to +1),2)) ] .

As you can see we can generate a list of subjects that are possible matches from the XYZ production for emails with the same date as the one we are checking from the ABC production.

The source of this formula is the Extend Office site.