Compare two columns and if match found check the next cell for a value and then return the result

Question

COL1        COL2              COL3
Hi          T_M12345678       T_455462    
            T_M12345670       T_M12345678
bye         T_M123456781      T_M12345670
            T_M123            T_M589646
            T_M894545         T_M123456781
            T_M418554651      
            T_M4546565

I need to compare COL2 and COL3; if any match is found then I need to compare with COL1 for that match found and if there is any value in COL1 then it should return a value on below mentioned scenarios true in COL4.

For Example,

• Scenario 1: Data T_M12345678 is present in COL2 and COL3 so match is found then, I need to check whether I have any value in COL1 for this data in COL2 and in this case, it is YES (Hi is the value in COL1) so I should print TRUE in COL4.

• Scenario 2: Data T_M12345670 is present in COL2 and COL3 so match is found; then I need to check whether I have any value in COL1 for this data in COL2 and in this case, it is NO so I should print TRUE1 in COL4.

• Scenario 3: Data T_M589646 in COL3 is not present in COL2 so I need to print FALSE in COL4.

Answer 1

Since you did not post the expected outcome, I created 2 additional columns (1 for values in COL2, other for values in COL3). The following formulas work as you defined.

COL2 value check:

=IFERROR(IF(AND(MATCH(B2,$C$2:$C$8,0),ISBLANK(A2)),"TRUE1","TRUE"),"FALSE")

COL3 value check:

=IFERROR(IF(AND(MATCH(C2,$B$2:$B$8,0),ISBLANK(A2)),"TRUE1","TRUE"),"FALSE")