Return the search value

Copper Contributor

I hope you are fine. I have asked this question twice before, but the answers were not suitable for me. I don't want to do this with Power Query. I want to search all 10064 words in column j in each of the cells in the column. And the result will be exactly the same words and written in column B facing each cell A. .
The values of column A are hypothetical. In reality, the column has about 40,000 cells. And if the results include several words in each cell of column B, put them in one cell and put a comma between each word. Please write the formula for me.

Thank you.

I use Excel 365

6 Replies

@jorj1991 

Hello please try this formula with FIND() and FILTER().

 

=IFERROR(TEXTJOIN(",",TRUE,FILTER($J$2:$J$10065,IFERROR(FIND($J$2:$J$10065,A2)>0,"")=TRUE)),"")

 

Good luck!

@jorj1991 

You are not clear on what you want to "search all 10064 words" to find, and the special characters in column A make it less clear.

 

I assumed that you want each line (separated by Line Feed characters, CHAR(10)) within a cell to be matched, including any leading and/or trailing spaces.  So, does this show the results you want (dscheikey's formula results shown in column C for comparison only) ?

jorj1991_1.png

(A6 and A9 do not match because the similar entries in column J have leading spaces.  I do not understand why the "Dried Fruit " portion of A2 does not match J7.)

 

My formula:

 

=IF(A2<>"", TEXTJOIN(",", TRUE, XLOOKUP( TEXTSPLIT(A2,CHAR(10)), J:J, J:J, "", 0, 1 ) ), "" )

 

 

Edit: Looking at it again, I see that J7 also includes a leading space, and thus does not match.

@jorj1991 

It might help others to look at solutions offered in other discussions from @jorj1991 .

 

Optimization and efficiency seem to be really important in this setup if previous solutions weren't acceptable.  It's not clear if memory errors are the reason those solutions didn't work but I'll suggest that one thing to try with any lookups being done is to clean up and sort the words list in ascending order.  Then try a VLOOKUP with an approximate match or even an XLOOKUP with a binary search.  The tradeoff is the lookups are not as accurate but may at least calculate.

Hello, thank you for your reply and the time you took to help me. I apologize for the delay in replying. I tested your formula. does not work right. Maybe I didn't execute well, but this formula works easily.
=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$I$2:$I$37&"*"), $I$2:$I$37, ""))

"I" is the column to be searched in cell "A2".

 

Thank you again dear friend

@SnowMan55 

Hello, thank you for your reply and the time you took to help me. I apologize for the delay in replying. I tested your formula. I don't want to do it with the find function because it is case sensitive. I did your formula with the search function. Compared to the formula below, it takes about 6 times the time. And the results of this formula with your formula in the number of characters in the test sample I have exactly the same size.
=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$I$2:$I$37&"*"), $I$2:$I$37, ""))

"I" is the column to be searched in cell "A2".


Thank you again dear friend
Hello, thank you for taking the time to write this answer. I apologize for the delay in answering. It works according to the formulas I have seen.
Thank you dear friend