If a Cell Contains a Certain Text (From Another Table)

[Bassem]

I have a very challenging exercise that I want to solve and of course with your help.

My Excel workbook contains two sheets.

Sheet 1 is my main sheet.
The cells in the first column (A) contain all the different codes.
And I am trying to check if this cell contains specific text.
In my example, the codes I am trying to search are listed in sheet 2. (e.g. 736, 739, H01, H05)

Meaning: If a Cell (A2) Contains a Certain Text (e.g. 736 or H05 ...etc.), then Put in cell B2 a Specified Text (Specific Code Name listed in the second sheet)

Sheet 2 contains the table of these codes (source)

And this is the final result that I am trying to achieve.

Would you please help me with which formula I shall use.
the sample excel sheet is attached as well.

Thank you in advance.

 

[bosco_yip]

B2, copy down :

=IFERROR(LOOKUP(1,-SEARCH(" "&Table1[Specific Code]&" "," "&[@[All Codes]]&" "),Table1[Specific Code Name]),"")

Regards

 

[Bassem]

B2, copy down :

=IFERROR(LOOKUP(1,-SEARCH(" "&Table1[Specific Code]&" "," "&[@[All Codes]]&" "),Table1[Specific Code Name]),"")

Regards

GREAAAAT!!!! Thanks a lot bosco_yip, it works

One last question, what if the (All Codes) format comes with commas as shown in the attached pic, what will be the formula in this case?

sorry for annoying.

 

[bosco_yip]

.......One last question, what if the (All Codes) format comes with commas as shown in the attached pic, what will be the formula in this case?......

Revise as per highlighted in red :

=IFERROR(LOOKUP(1,-SEARCH(" ,"&Table1[Specific Code]&" ,"," ,"&[@[All Codes]]&" ,"),Table1[Specific Code Name]),"")

Regards

 

[Bassem]

Revise as per highlighted in red :

=IFERROR(LOOKUP(1,-SEARCH(" ,"&Table1[Specific Code]&" ,"," ,"&[@[All Codes]]&" ,"),Table1[Specific Code Name]),"")

Regards

Thank you very much bosco_yip, it works like magic