Forum Discussion
Formulas
I'd also recommend to re-design right table a bit as
with Helper column calculated as
=TRIM(IFERROR( RIGHT( $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)), LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) - FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))), $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))Status could be calculated as
by formula
=IF(COUNTIFS( $G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)), $A2, $I$3:$I$9, IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2) ),"No Error", "Error")
You can use LOOKUP function and make this dynamic array.
LOOKUP(field to search, first column where search is being done, what to return when search is matched).
Try this and confirm if this is feasible solution.
Please find the attached sample file and help him to get the desired result.
Column A- Category
Column B- Notes
Column C- Manually I checked whether Category matching with the notes or not
Column D- Please add the formula to check whether the correct notes selected for correct category
Note: CM- Account Credit (contl) and CM - Account Transfer (contl)----Number-Notes( No Spaces)
For Category CM - Account Closed (contl)- notes should be without number ie Closed or Dormant
Could you please clarify the logic a bit more. For example, in left table
CM- Account Credit (contl) 1- credit Error having 1-credit for this category in right table, but
CM- Account Credit (contl) 3-credit No Error has no error. If we ignore numbers, both shall return No Error. If not, it shall be an opposite.
SergeiBaklan - There is space after hyphen, so it is error.
CM- Account Credit (contl) 1- credit Error
