1. ## Match and Index

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

2. 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).

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

4. Hi eisayev

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

Regards

Originally Posted by eisayev
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

5. 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)))

6. 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?)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•