Forum Discussion
FORMULA TO MATCH TEXT STRING IN 2 COLUMNS
Thanks for explaining the logic.
Please try this...
In D2
=IF(COUNTIF($B$2:$C$3000,"*"&A2&"*"),"MATCH","NO MATCH")
and copy it down.
I have placed the formula in Green Cells in column E in the attached.
Please let me know if you are getting the desired output now.
Subodh_Tiwari_sktneer hi. i ma 4 years late to the party. Thank you. This formula will be very helpful to me. Is it possible to get matching sub-text in a separate column? Thank you.
Taking the same example, let's say you want to return text from the matching row in column F:
=LET(n, MIN(IFERROR(MATCH("*"&$A2&"*", $B$2:$B$5, 0),1000000000), IFERROR(MATCH("*"&$A2&"*", $C$2:$C$5, 0), 1000000000)), IF(n<>1000000000, INDEX($F$2:$F$5, n), ""))
(1000000000 is an arbitrary very large number)
HansVogelaar I tried your formula, butI am getting #Name? error message. Thank you for your help.
The formula that I posted will work in Excel in Microsoft 365 and Office 2021. In an older version, you should be able to use
=IF(MIN(IFERROR(MATCH("*"&$A2&"*", $B$2:$B$5, 0),1000000000), IFERROR(MATCH("*"&$A2&"*", $C$2:$C$5, 0), 1000000000))<>1000000000, INDEX($F$2:$F$5, MIN(IFERROR(MATCH("*"&$A2&"*", $B$2:$B$5, 0),1000000000), IFERROR(MATCH("*"&$A2&"*", $C$2:$C$5, 0), 1000000000))), "")
