Vlookup - Returning 2nd match

  • Thread starter Thread starter MarkinArk
  • Start date Start date
M

MarkinArk

Quick question on returning the 2nd match of a vlookup . I am tracking
multiple major league baseball teams' win/loss streaks, and have the results
listed by date. I was doing fine, until the Mets and Dodgers had a
double-header yesterday, and now had 2 entries for that date. I know I can
test for multiple dates using Countif(A1:A4 4/27/2010) but don't know how to
return the 2nd match of the row of the vlookup.
A B
4/26/2010 POSTPONED
4/27/2010 W 4-0
4/27/2010 W 10-5
4/28/2010 W 7-3

vlookup(4/27/2010, a1:b4, 2, false) so that I get W 10-5 (where the first
vlookup returns W 4-0). Thanks for any help.

Mark
 
Try the below with lookup value in cell C1 and the lookup instance in cell
C2. In your case the lookup instance is 2.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(B1:B100,SMALL(IF(A1:A100=C1,ROW(A1:A100)),C2))
 
Back
Top