Forum Discussion

neiljacobson's avatar
neiljacobson
Copper Contributor
Dec 15, 2019
Solved

Wildcard in Match

Why does a wildcard in the Match function return a n/a? Here is a screenshot showing it. https://www.dropbox.com/s/bl37mg5hgs4zmaf/Screenshot%202019-12-15%2010.45.47.png?dl=0
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Dec 16, 2019

    neiljacobson 

    Wyn Hopkinsshould be around and starting his day about now.

    My comments would be that I would expect 'Mylist' to refer to a single column of keywords (your INDEX formula has only a row parameter).  I would expect the row number to be one of {1;2} so, to start with, you could use this array constant in place of ROW($1:$2) [number larger than the length of your keyword list will give rise to #REF! errors].  The final point is that I expected that it should be the elements of 'Mylist' that you are searching for in the text description. Overall, something like 

    = INDEX( Mylist, SUMPRODUCT( ISNUMBER( SEARCH(Mylist, Description) ) * {1;2} ) )

Wyn Hopkins's avatar
Wyn Hopkins
MVP
Dec 15, 2019
Sorry, I don't quite understand.

Can you clarify what you mean by extract the word?
neiljacobson's avatar
neiljacobson
Copper Contributor
Dec 15, 2019
Cell a1 has a whole string of words. table c1:d2 has the words i want to extract. Think of a bank statement that has descriptions of transactions. I have a table of short descriptions I want to use instead of the long descriptions that comes from the bank. Does that help?
    • neiljacobson's avatar
      neiljacobson
      Copper Contributor
      Dec 16, 2019

      Wyn HopkinsNow I get a #Name error. Here is the screenshot https://www.dropbox.com/s/azv3nzpo8poa7s5/Screenshot%202019-12-16%2008.22.33.png?dl=0

       

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Dec 16, 2019

      Wyn Hopkins 

      A formula that could now be added to Mynda's solution possibilities is

      = TEXTJOIN( ", ", TRUE, IF( ISNUMBER( SEARCH( list, text ) ), list, "" ) )

      or, if one really wishes to exploit the latest functionality

      = FILTER( list, ISNUMBER( SEARCH( list, text ) ) )

       

       

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Dec 16, 2019

        Nice PeterBartholomew1 ,

         

        TEXTJOIN must be array entered in "old" Excel  (Ctrl Shift Enter)

        {= TEXTJOIN( ", ", TRUE, IF( ISNUMBER( SEARCH( list, text ) ), list, "" ) )}

         

        Good use of FILTER too (not widely available yet neiljacobson )

        Could also wrap that FILTER in a TRANSPOSE to get single row results (or again use TEXT join)