If my cell has matching contain from list then output would be from another list.

[Mehmud Khan]

Hello friends,

Good day.

I need your help in below situation;

workfile;


master-data;


I have 2 tables, 1 table is my workfile and another table is master-data.



The output I want is like below;




If the colum A1 (and so on) has matching word from table "I" then output in colum "B" must be colum "J".

Please anybody can help.

Thanks in advance to all my frnds.



Regards,
Khan.

 

[ETAF]

a sample spreadsheet would be better here

is it just those 3 words
what if 2 or more appears

 

[Mehmud Khan]

Hi,

Thanks for reply.

The workfile has more than 1000 lines......

is there any formula that auto pick?

Regards,
Khan

 

[mohamed ilyas]

Hi,

Thanks for reply.

The workfile has more than 1000 lines......

is there any formula that auto pick?

Regards,
Khan

Try this.


=IF(ISNUMBER(SEARCH("*good*",A1)),"Band A",IF(ISNUMBER(SEARCH("*awesome*",A1)),"Band B",IF(ISNUMBER(SEARCH("*excellent*",A1)),"Band C","No Band")))

 

[pecoflyer]

Hi,

Thanks for reply.

The workfile has more than 1000 lines......

is there any formula that auto pick?

Regards,
Khan

You can trim it down if you like, but 1000 rows is nothing special

 

[Mehmud Khan]

Try this.


=IF(ISNUMBER(SEARCH("*good*",A1)),"Band A",IF(ISNUMBER(SEARCH("*awesome*",A1)),"Band B",IF(ISNUMBER(SEARCH("*excellent*",A1)),"Band C","No Band")))

Hi iliyas,

Thanks you so much buddy....

101% it works... this is exactly I was looking for.....


@pecoflyer
@ETAF

Thanks to you guyz... for your helps...

But I think iliyas understood my query...



thanks once again.


as always regards,
-Khan