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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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