##### New Member
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

##### MrExcel MVP
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.

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

Replies
1
Views
600
Replies
0
Views
543
Replies
4
Views
368
Replies
0
Views
747
Replies
3
Views
893

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.

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.

### Which adblocker are you using?

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

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