CODEWITHSUNDEEP
excel-formulamatchtextjoin
Robert Hall
Robert Hall

Reputation: 191

Ignore cells that contain a certain word using textjoin/match formula

The below formula compares two tables to match names and then returns those names it cannot find in the second table. What i would like it to do is exclude any names that contain the word Agency, note that the STB is varible and can change. So for example in the picture, it would just return Joe Bloggs.

Table 1                           Table 2

E-Name of employee                Name
Joe Bloggs                        Karin Matthews   
Joe Bloggs                        Tony Williams
Jane Doe                          Bill Johnson
Jane Doe
Agency STB Agency STB
Agency STB Agency STB
Agency STB Agency STB
Karin Mathews
Tony Williams
Bill Johnson


=TEXTJOIN("",TRUE,IF(ISERROR(MATCH(HrsRoleEchoPay[E-Name of employee],AwardsFromEchoTbl[Name],FALSE)),HrsRoleEchoPay[E-Name of employee],""))

enter image description here

Upvotes: 1

Views: 834

Answers (2)

P.b
P.b

Reputation: 11483

For older Excel the formula would be a bit longer:

=TEXTJOIN("",1,
          INDEX(HrsRoleEchoPay[E-Name of employee],
             AGGREGATE(15,6,
                (ROW(HrsRoleEchoPay[E-Name of employee])-ROW(HrsRoleEchoPay[[#Headers],[E-Name of employee]])) 
                /(NOT(ISNUMBER(SEARCH("Agency",HrsRoleEchoPay[E-Name of employee])))) 
                /(COUNTIF(AwardsFromEchoTbl[Name],HrsRoleEchoPay[E-Name of employee])=0), 
             ROW(A1:INDEX(A:A, 
                          SUM((NOT(ISNUMBER(SEARCH("Agency",HrsRoleEchoPay[E-Name of employee]))))
                          *(COUNTIF(AwardsFromEchoTbl[Name],HrsRoleEchoPay[E-Name of employee])=0)))))))

Upvotes: 1

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

With Office 365 you can do:

=LET( EN, HrsRoleEchoPay[E-Name of employee],
       N, AwardsFromEchoTbl[Name],
       F, FILTER( EN, NOT(ISNUMBER( FIND( "Agency", EN ) )) ),
       TEXTJOIN("",TRUE,IF(ISERROR(MATCH(F,N,FALSE)),F,"")) )

Upvotes: 2

Related Questions