Vlookup - Returning 2nd match

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

See
http://www.ozgrid.com/VBA/ultimate-e...p-function.htm



--
Regards
Dave Hawley
www.ozgrid.com
"MarkinArk" wrote in message
...
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)),C 2))

--
Jacob (MVP - Excel)


"MarkinArk" wrote:

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

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MarkinArk" wrote in message
...
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

On 29 Apr., 04:48, "ozgrid.com" wrote:
Seehttp://www.ozgrid.com/VBA/ultimate-excel-lookup-function.htm

--
Regards
Dave Hawleywww.ozgrid.com"MarkinArk" wrote in message

...

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

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