Search code examples
excelexcel-formulalogic

Multiple criteria column matching in excel

I am trying to check weather certain themes from columns "match" (but are not identical) based on certain criteria. Please see example in the picture.

I am trying to check if column A = column G, then check if Column B = "Risk" and Column H = "Brand & Reputation", then it is correct, otherwise not. I have more conditions but I can include them once I make this work.

This is what I have tried so far:

=IFS(AND([@ID]=G:G; AND([@[Manual Theme]]="Risk";H:H="Brand & Reputation"));1)

What am I doing wrong?

enter image description here

Solution

  • Instead of IFS( ) try using XMATCH( ) or if you don't have it, then use MATCH( ) function.

    enter image description here

    • Formula used in cell C2

    =IF(ISNUMBER(XMATCH(1,([@ID]=Table4[ID])*([@[Manual Theme]]="Risk")*(Table4[Automatic]="Brand & Reputation"))),1,"")

    The above formula specifically looks in for the single word string Risk in Manual Theme Column, but if want it to search in a string where other words are present as well Risk then try using the below one.

    enter image description here

    • Formula used in cell C2

    =IF(ISNUMBER(XMATCH(1,([@ID]=Table4[ID])*(ISNUMBER(SEARCH("Risk",[@[Manual Theme]])))*(Table4[Automatic]="Brand & Reputation"))),1,"")

    Or,

    enter image description here

    • Formula used in cell C2

    =(INDEX(Table4[Automatic],XMATCH([@ID],Table4[ID]))="Brand & Reputation")*(ISNUMBER(SEARCH("Risk",[@[Manual Theme]])))

    Note: Change the cell references and structured references as per your suit.