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

excel

6 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 30, 2022

    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.

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

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Oct 30, 2022

    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'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. 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