Cell contains specific word and return that specific word in another cell

[soniba2]

Hi Team,

Greetings! Hope everyone is safe and doing well.

I am new to excel and learning few of its functions/formula on daily basis. I need help on below criteria, that seems to be advanced one, Please could someone review and suggest to get to the result.

Excel version: 2016

Worksheet#1 [Final Data] [around 6000 rows]

Column:A Row:1	Column:B	Column:C	Column:D
Row:2	No.	Country [Result to be displayed here, i.e. Country Name]	Description
Row:3	1	Egypt	The pyramids are in Egypt.
Row:4	2	Japan	Japan is in APAC region
Row:5	3	Australia	The Red Kangaroo is the national animal of Australia.
---	---	---	---
Row:n; n = 6000	6223

>>  please note that result word (country name) can be either in start or middle or end of the description.

Worksheet#2 [Country List] [includes single column of number of countries]

Column: c = list of countries

Japan

India

France

Egypt

China

Paris

......

Logic: look the description (column:D, worksheet#1) and find specific word (country name), search or lookup that country name in (column:c, worksheet#2 - i gave a name list (for workbook) to it, i.e. to column: $c:$c = clist (country list) and return value in Column:C (worksheet#1).

I found below formula but that is array and i have around 6000 rows, also other formulas - which results in lot of processing and excel goes hung. 

ref.: 

https://www.get-digital-help.com/if-cell-contains-text-from-list/

Formula: =IFERROR(INDEX($G$3:$G$7, SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"), MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1))), "")

used below formula but super slow and excel hungs-

IFERROR(INDEX(clist,SMALL(IF(COUNTIF($C3,"*"&clist&"*"),MATCH(ROW(clist),ROW(clist)),""),COLUMNS($A$1:A1))),"") CTRL+SHIFT+ENTER [array formula]

Question:

is there any other way of applying formula so excel won't hung and speed can be optimized even with more data to be populated?

Appreciate your advise on the same..

Thanks!

Baiju

[#2 · Accepted solution]

@soniba2 The attached solution may work for you.

[#3]

@Riny_van_Eekelen 

Thank you very much! it helped..and is working fast with negligible slowness due to the bunch of data and formula.

i understand that we have created table for the countries list, apart from that other areas - i couldn't determine...

can you please also, advise to get better on the excel - with its use and formulas? 

Best Regards,

Baiju

[#4]

@soniba2 

Indeed, I put the country list in a structured table. You can add countries to the list without having to worry about expanding the ranges used in the formula. You can move it anywhere you like in your workbook, as long as the table is called "Countries" and the column header "Country". But, I could have used a regular reference to the list or I could have created a dynamic named range the country list. Many different options!

Now, as for the formula, start reading it inside out.

=IFERROR(INDEX(Countries[Country],MATCH(TRUE,(ISNUMBER(SEARCH(Countries[Country],C3,1))),0),1),"")

SEARCH(Countries[Country],C3,1) determines if any of the countries in the list exists in cell C3, starting at the first character. It will return a number for the position (=index) in the list if found.

ISNUMBER(-----------------) will return TRUE if the country was found, FALSE if not found.

MATCH(TRUE,(-------------),0) will return the index if TRUE ar and error is FALSE. The zero at the end means it looks for an exact match.

INDEX(Countries[Country],--------,1) will return the name of the country based on the result from the previous step.

IFERROR(----------,"") captures the error in the MATCH step (if applicable) and returns a blank, rather than the error message. You can enter, e.g. "No country name found" in stead of "".

Having said all this, note that there is one weakness with this approach. Should you have sentence like "Japanese people live in France", it will return Japan as a part of "Japanese" and because Japan is in the list before France.

[#5]

@Riny_van_Eekelen 

thank you! Riny for the explanation.

yes, your point is valid if two or more countries in description cell is mentioned; need to figure out on the same.

Thanks!

Baiju