Results 1 to 6 of 6

Thread: Match and Index

  1. #1

    Match and Index



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #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).
    Attached Files Attached Files

  3. #3
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    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. #4
    Hi eisayev

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

    Regards



    Quote Originally Posted by eisayev View Post
    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. #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. #6
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    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
  •