VLOOKUP,INDEX,MATCH = WRONG ANSWER!!!!!!

ATMlady

New Member
Joined
Jan 20, 2003
Messages
37
Here's my problem. I have:

A B C
Customer Contract OpenDate
1 5055 Bats 11/15/05
2 6100 Balls 08/17/04
3 6100 Bats 06/16/05
4 8750 Hats 12/01/05


I've got a vlookup that will pick up the first instance of 6100 and it's open date. To pick up the second instance of 6100, I came up with this formula after reviewing similar posts on this site:

=VLOOKUP(INDEX($A$1:$C$4,MATCH(A3,$A$1:$A$4,1)+1,1),$A$1:$C$4,3,FALSE)

but the results are for 8750! It's like it's MATCH-ing the second instance of 6100 instead of the 1st! Arg!!!!! Can anyone twll me what I'm overlooking?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ATMlady said:
Here's my problem. I have:

A B C
Customer Contract OpenDate
1 5055 Bats 11/15/05
2 6100 Balls 08/17/04
3 6100 Bats 06/16/05
4 8750 Hats 12/01/05


I've got a vlookup that will pick up the first instance of 6100 and it's open date. To pick up the second instance of 6100, I came up with this formula after reviewing similar posts on this site:

=VLOOKUP(INDEX($A$1:$C$4,MATCH(A3,$A$1:$A$4,1)+1,1),$A$1:$C$4,3,FALSE)

but the results are for 8750! It's like it's MATCH-ing the second instance of 6100 instead of the 1st! Arg!!!!! Can anyone twll me what I'm overlooking?

Lookup functions are designed to return the first matching item. Put otherwise, they expect data with distinct records.

If AutoFilter does not meet your needs, try instead:
Book3
ABCDEF
1CustomerContractOpenDate
25055Bats11/15/20056100
36100Balls8/17/20042
46100Bats6/16/20058/17/2004
58750Hats12/1/20056/16/2005
6 
7
Sheet1


F3:

=COUNTIF(A2:A5,F2)

F4:

=IF(ROWS($F$4:F4)<=$F$3,INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=$F$2,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:F4))),"")

which is confirmed with control+shift+enter (not just with enter) then copied down.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I suspect you were trying to do something like this
Book1
ABCDEFGH
15055Bats11/15/05lookup1st2nd
26100Balls08/17/04610008/17/0406/16/05
36100Bats06/16/05
48750Hats12/01/2005
5
Sheet2


Formula in G2

=VLOOKUP(E2,INDEX($A$1:$A$4,MATCH(E2,$A$1:$A$4,0)+1):$C$4,3,0)

Probably only good for a second match, not multiple matches
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,308
Members
412,716
Latest member
thviid
Top