Vlookup - Returning 2nd match

[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

 

[ozgrid.com]

See
http://www.ozgrid.com/VBA/ultimate-excel-lookup-function.htm

 

[Jacob Skaria]

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))

 

[Ashish Mathur]

Hi,

You may refer to my article here -
http://office.microsoft.com/en-gb/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bernd P]

Seehttp://www.ozgrid.com/VBA/ultimate-excel-lookup-function.htm

Hello Dave,

That's some nice piece of code.

Two suggestions: You can enhance the functionality to lookup the last
value of a range (for example Occurence = -1) or the last but one
(Occurrence = -2), etc. by changing the search direction if Occurence
is negative (and taking -Occurrence). And I would omit Column_Lookin
to shorten the code:
http://sulprobil.com/html/lookup-variants.html

Regards,
Bernd