CODEWITHSUNDEEP
excel-formula
Lauren P
Lauren P

Reputation: 55

If cell contains item from range AND meets criteria in other column in range then return value

I am hoping for further help on my bird research study. Everyone has been so helpful and I'm using all of your suggestions, it's really helped me get through 6500+ cases!

Here is my new dilemma: I want to check a cell (DIAGNOSIS FOR EACH CASE) to see if it includes any string from a range of items (ALL DIAGNOSES) AND is also marked "Y" for a qualifier (SHOCK, in this example) for that item in the range.

I have tried =IF(SUMPRODUCT(--ISNUMBER(SEARCH($B,D2:D4)))>0,"Y","N") placed in $C and this has gotten me half way there, but I need this second qualifier and I can't seem to be able to do it by myself.

In this example, Column B is the search-in column, Column D is the range, Column E is the qualifier for the range, and Column C is the desired output. I would prefer to do this without having to convert my data to tables.

CASE DIAGNOSES FOR EACH CASE DESIRED OUTPUT ALL DIAGNOSES (RANGE) SHOCK (RANGE)
1 SHOCK, PULMONARY; HYPOXIA Y SHOCK Y
2 SHOCK Y SHOCK, PULMONARY Y
3 SHOCK, PULMONARY Y HYPOXIA N
4 N
5 HYPOXIA N

Upvotes: 1

Views: 104

Answers (2)

nkalvi
nkalvi

Reputation: 2614

  • filter "ALL DIAGNOSES" and sort for faster lookup with XMATCH
  • split each diagnoses entry by ' and lookup
  • at least one is matched if
    SUM(--ISNUMBER(XMATCH(TRIM(TEXTSPLIT(d_e, {",",";"})), all_diag > 0

enter in C2 (and adjust the ranges to match your data):

=LET(
    include_for_shock, E2:E100 = "Y",
    all_diag, SORT(TRIM(FILTER(D2:D100, include_for_shock))),
    diag_for_each, B2:B100,
    includes_diag, MAP(diag_for_each, LAMBDA(d_e,
            IF( ISBLANK(d_e),0, SUM(--ISNUMBER(XMATCH(TRIM(TEXTSPLIT(d_e, ";")), all_diag, , 2))) ))
    ),
    IFS(ISBLANK(diag_for_each), "", includes_diag, "Y", TRUE, "N")
)

Result

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Here is one way you could try :

enter image description here

=IF(OR(IFERROR(SEARCH($D$2:$D$4,TEXTSPLIT(B2,"; ")),0)*($E$2:$E$4="Y")),"Y","N")

Or, If you like to return it for the whole array then:

=MAP(B2:B6,LAMBDA(x,IF(OR(IFERROR(SEARCH(D2:D4,TEXTSPLIT(x,"; ")),0)*(E2:E4="Y")),"Y","N")))

Can use the TEXT() function to return the Y and N as well or custom formatting like in last query:

=TEXT(N(OR(IFERROR(SEARCH($D$2:$D$4,TEXTSPLIT(B2,"; ")),0)*($E$2:$E$4="Y"))),"[=1]\Y;\N")

Also, if applicable can make use of the newer function available in MS365 Office Insiders, TRIMRANGE() or its features.

=LET(
     _Search, DROP(B.:.B,1),
     _Range, DROP(D.:.E,1),
     MAP(_Search,LAMBDA(x,
     IF(OR(IFERROR(SEARCH(TAKE(_Range,,1),TEXTSPLIT(x,"; ")),0)*
          (DROP(_Range,,1)="Y")),"Y","N"))))

Upvotes: 0

Related Questions