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