Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 464
Default Vlookup - Returning 2nd match

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Vlookup - Returning 2nd match

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Vlookup - Returning 2nd match

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Vlookup - Returning 2nd match

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup Match BUT Returning #NA DaveM Excel Discussion (Misc queries) 1 December 27th 08 11:58 PM
vlookup not returning a match even when there is one karen Excel Worksheet Functions 6 July 14th 08 02:09 PM
VLookup is not returning the first match data Pogue Excel Worksheet Functions 6 December 7th 07 10:59 AM
need help with a vlookup but returning a particular match? D7ONO Excel Worksheet Functions 4 May 5th 06 02:14 PM
VLOOKUP returning LAST match Brian Ferris Excel Discussion (Misc queries) 1 April 4th 05 02:00 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"