How would I return a partial string to a calculated column based on a look up table-CodePudding

I need to parse cell for partial string match equal to any of the items in the look up table and return the first match (It would also be nice if it can return multiple matches if there happened to be more than 1 country in the comma separated cell * not required as this would be rare and can be covered by Multi country if needed*)

=IFS(SEARCH(L3,[@Tags]),SEARCH(L4,[@Tags]),SEARCH(L5,[@Tags]),SEARCH(L6,[@Tags])) result in errors I've also tried it using nested IF statements . I've also tried find in place of search

See link for example of Data set :

Formula in column D is:

=INDEX($I$2:$I$5;SUMPRODUCT(COUNTIF(B2;"*"&$I$2:$I$5&"*")*ROW($I$2:$I$5))-1)

Because ROW returns the row number related to whole worksheet but data starts at row 2, you need to substract minus 1.

Also, if you have Excel 365, probably you can use advanced functions to return more than 1 entry.

CodePudding user response：

See if the following works for you:

Formula in D2:

=LOOKUP(99^99,SEARCH(","&Table2[Countries]&",",","&[@Tags]&","),Table2[Countries])