Forum Discussion

jorj1991's avatar
jorj1991
Tin Contributor
Oct 30, 2022

Return the search value

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 eac...
excel
SnowMan55's avatar
SnowMan55
Bronze Contributor
Oct 30, 2022

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) ?

(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's avatar
jorj1991
Tin Contributor
Nov 11, 2022

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