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

Question

Hello all, I need to check if content of a string is matching 2 columns in excel. Example what to type in cell B2, B3, B4, etc., to check the occurrence of cyproheptadine-levetiracetam or levetiracetam-cyproheptadine combination in cell A2, A3, A4, etc.. Your help is highly appreciated. Thank you, Ahmed

Answer 1

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)..

Answer 2

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