• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find one text value with multiple text ?

FauzanOmar

New Member
Hi Expert,

Do you know how to put formula on the yellow table below?
Basically, If I type NORA-ADAM, the cell will automatically show SAMSUNG/XIAOMI.
If ADAM-NORA, the cell will show XIAOMI/SAMSUNG.
If ADAM-MICHEAL, the cell will show no value.

78456

Thank you
 
This work for my Excel 2019.

In B7, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(TEXTJOIN(" / ",,INDEX(B$2:B$3,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(A7,"-","</b><b>")&"</b></a>","//b"),A$2:A$3,0))))),"NIL")

78460
 
This work for my Excel 2019.

In B7, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(TEXTJOIN(" / ",,INDEX(B$2:B$3,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(A7,"-","</b><b>")&"</b></a>","//b"),A$2:A$3,0))))),"NIL")

View attachment 78460
Wow, you really an expert! I didn't expect the formula will be that complex. Thanks a lot for your time solving this problem.
 
Back
Top