How to check if a string is matching 2 criteria from 2 column

If you are looking for the exact substrings "cyproheptadine-levetiracetam" and "levetiracetam-cyproheptadine", you can use a formula in B2 of the form IF(OR(FIND(C2&"-"&D2,A2)>0,FIND(D2&"-"&C2,A2)>0),"X"," ") and copy down to as many rows as needed. This will put an X in B2 and B3 but not B4. If you are looking for both drug names to be present with any possible intervening text, you can use a formula in B2 of the form IF(AND(FIND(C2,A2)>0,FIND(D2,A2)>0),"X"," ") and copy down to as many rows as needed. This will put an X in B2, B3, and B4 (assuming you populate C4 and D4)..

It would have been nice if you told me which options were correct.

• You need to create an Excel macro function that returns a string. Assuming you want to check all the entries in columns E and F, the macro does not need any arguments. You invoke the macro in each cell in column B that you want to check the corresponding cell in column A.
• The macro will need an initialization phase and then a processing loop.
• For initialization, the macro needs to determine which cell is was called from and which row has the last entries in columns E and F.
• In the loop, the macro starts at the first entry in columns E and F. It checks to see if the text in both cells is present in the column A cell. If true, It adds to the return string the value of column E followed by a blank (or hyphen) followed by the value in column F and then exits. If false, it proceeds to the next pair of entries in columns E and F. If it goes past the last entries in E and F, it returns the null string.

If you are familiar with Excel macros, there should be nothing difficult in this. Once you get this working, we can discuss checking for multiple interactions

Hi,

Enter this formula in cell B2

=BYROW(A2:A4,LAMBDA(a,ARRAYTOTEXT(TOROW(IF(LET(l,XMATCH($D$2:$E$3,TRIM(TEXTSPLIT(a,{"-","/"}))),BYROW(ISNUMBER(l),AND)*(CHOOSECOLS(l,1)=1)),$D$2:$E$3,NA()),3))))

Hope this helps.

I might do it differently if your data is just slightly differen.

For the Poster, you said basically want to check for x & y, or y & x.

My opinion is that it would be even more efficient if you only listed the pair once.

This new posting format is a little messed up if you ask me. I "think" I can only post 1 image.

I would do it like the second group. Since you have the information already, you may want to consider adding muliple matches like the last line.

Hi,

Try this formula in cell B2:

=SUBSTITUTE(BYROW(A2:A4,LAMBDA(a,LET(s,SUBSTITUTE(a,"-"," "), TRIM(SUBSTITUTE(s,TRIM(REDUCE(s,UNIQUE(TOCOL(E2:F20)),LAMBDA(a,b,SUBSTITUTE(a,b,"")))),"")))))," ","-")

or this foemula which places each data into different columns:

=IFERROR(TEXTSPLIT(TEXTJOIN(";",,BYROW(A2:A4,LAMBDA(a,LET(s,SUBSTITUTE(a,"-"," "),TRIM(SUBSTITUTE(s,TRIM(REDUCE(s,UNIQUE(TOCOL(E2:F20)),LAMBDA(a,b,SUBSTITUTE(a,b,"")))),""))))))," ",";",TRUE),"")

Adjust the ranges in the formulas according to your needs.

HTH.