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

Bisso · Mar 11th 2017

Hi There,

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

My excel workbook contains two sheets.

The first sheet 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, codes I am trying to search for are H01, H02, H03 ...etc.)
Meaning: If a Cell (A2) Contains a Certain Text (e.g. H05 ...etc.), then Put in cell B2 a Specified Text (H Code Name listed in the second sheet)
[Blocked Image: https://i.gyazo.com/d349a1eba085f323f97432a00f359dd7.png]

The second sheet contains the table of these H codes (source)
[Blocked Image: https://i.gyazo.com/4614d75a2c5b9e8601411d5aefdd7b25.png]

and this is the final result that I am trying to achieve.
[Blocked Image: https://i.gyazo.com/75e95d9fb5e5a45d191e1441015036f2.png]

Would you please help me in this.
the sample excel sheet is attached as well.

Thanks

**Carim** · Mar 11th 2017

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

Hello,

You could test the following :

Code

=INDEX(Table1[[#All],[H Code Name]],MATCH(MID(A2,FIND("H",A2,1),3),Table1[[#All],[H Code]],9))

HTH

Bisso · Mar 11th 2017

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

Thanks a lot Carim .. it works

**Carim** · Mar 11th 2017

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

Glad it did help you out ...:wink:

Thanks for ... your thanks ... :smile:

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

Participate now!

Share