Correct formula to get correct match

  • Hello Formula experts

    With the help of a formula I am trying to separate the matched rows from the mismatched rows with multiple conditions. I need your help me to get only one single match (the first match in the list) from one each from Portal and one from Books. The problem with this formula is that if the Portal has multiple same values under one GSTIN number and a single value in Books then it is displaying all the rows as matched. I want the formula to take just one row of Portal and one row of Books only. It there are 2 rows of same value in both portal and books then only it has to show them both matched.

    I have shown the expected result in column O.

    Thank you in advance.

    This is the formula which shows as all matched which is incorrect. I want this formula to be edited.

    =IF(IF($G2<>"",COUNTIFS($B:$B,IF($B2="PORTAL","BOOKS","PORTAL"),$C:$C,$C2,$G:$G,">="&($G2-1),$G:$G,"<="&($G2+1)),COUNTIFS($B:$B,IF($B2="PORTAL","BOOKS","PORTAL"),$C:$C,$C2,$H:$H,">="&($H2-1),$H:$H,"<="&($H2+1),$I:$I,">="&($I2-1),$I:$I,"<="&($I2+1)))>0,"MATCHED","Not Found")

    Formula to correct.xlsx

    The above query stands solved.

    Edited 4 times, last by RAJESH SHAH: # Solved. ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!