Forum Discussion
Cell contains specific word and return that specific word in another cell
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
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
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.
