Excel Formula to compare two columns and if match found check the next cell for a value and then return the result

0
votes
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.

1
excelexcel-formulaformulas
I am able to compare 2 columns and set true or false.. But I want the result of that comparison to be evaluated further to another column and set an value – chengaiah
Your current formula is very welcome here. It's much easier to help when we have something real. Please edit your question to add additional info. – ZygD

1 Answers

1
votes

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

enter image description here