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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,625
Messages
5,832,750
Members
430,163
Latest member
YesImAk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top