Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formuladata-extractionkeyword-search

Check for unique keywords in cell data in google sheet

I have a list of email subject lines with job roles on the left side, need to filter out roles like Assistant Manager, Manager, Civil Engineer, Project Manager to get the result.

Certain cells don't have the job role mentioned so to be left blank

The rows on the sheet need to remain intact so data can be analysed.

i tried using REGEXMATCH but it will not work for row5 is there a way to have a single formula, which includes all keywords for the whole list.

REGEXMATCH(B3, "assistant|manager")

I'm doing this on Google sheet and have shared the screenshot and the GoogleSheet file Link for reference.

enter image description here

Solution

  • I have made a copy of your sample data on this Support Sheet, that will exist in perpetuity.

    On a tab called MK.Lookups, I built a table to use for your lookup words and returns. Then on the tab called MK.Idea, I placed this formula in cell B3 and dragged it down.

    =IFERROR(INDEX(SPLIT(TEXTJOIN("|",TRUE,IF(REGEXMATCH(UPPER(B3),SUBSTITUTE(UPPER(MK.Lookups!C$2:C$20),", ","|")),MK.Lookups!B$2:B$20,)),"|"),1))

    I also placed this formula in cell C1, which is an arrayformula that will populate for every row and doesn't need to be copied down the column.

    ={"Result";ARRAYFORMULA(IFERROR(INDEX(SPLIT(TRANSPOSE(TRIM(QUERY(IF(REGEXMATCH(TRANSPOSE(UPPER(A2:A)),SUBSTITUTE(UPPER(MK.Lookups!C$2:C$20),", ","|")),MK.Lookups!B$2:B$20&CHAR(10),),,9^9))),CHAR(10)),,1)))}