Match and Index

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
Good day

I am have trouble with the match function on the attached sheet. I have number of duplicate in column B and when I match it returns the value of the first duplicate in the range instead of going to the next and so forth. (i, cell G20 matches row 27 and the cell G21 should match row 28).

you help will be appreciated
 

Attachments

  • MATCH & INDEX.xls
    35 KB · Views: 25

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
I didn't test extensively, so can't say for sure this will work but you can try it out and see .Column J contains new adjusted formula(s).
 

Attachments

  • match_duplicates.xlsx
    15.7 KB · Views: 20

eisayev

New member
Joined
Oct 1, 2012
Messages
33
Reaction score
0
Points
0
Location
Baku, Azerbaijan
I tried it ; it works
=INDEX(OFFSET($B$1;MATCH(F20;B:B;0)-1;0;COUNTIF(B:B;F20);2);COUNTIF($F$20:F20;F20);2)
copy past it to the cell u wanna get the result
good luck
try it; if it doesnt work i will send to u with the file
 

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
Hi eisayev

I have tried it but it does not work. can you please send me the file.

Regards



I tried it ; it works
=INDEX(OFFSET($B$1;MATCH(F20;B:B;0)-1;0;COUNTIF(B:B;F20);2);COUNTIF($F$20:F20;F20);2)
copy past it to the cell u wanna get the result
good luck
try it; if it doesnt work i will send to u with the file
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try this array formula

=INDEX($C$1:$C$41,SMALL(IF($B$1:$B$41=SMALL($B$1:$B$41,ROW(A1)),ROW($B$1:$B$41)),ROW(A1)))
 

eisayev

New member
Joined
Oct 1, 2012
Messages
33
Reaction score
0
Points
0
Location
Baku, Azerbaijan
Hi, I tried to attach the excel file but I couldnt. Sorry for that. I am new here. almost 16 hours ;)
Just copy and paste the formula to the cell u wanna get result. it is not an array formula
(Anybody can tell me how to attach the file?)
 
Top