Forum Discussion

James Park's avatar
James Park
Copper Contributor
Feb 25, 2018

searching values in part of another column and/or cell

Hello,   I'm attempting to look-up/search specific portions of a cell that's within another cell. Reference the attached test sheet as I try to explain exactly what I'm attempting to do..   (...
Excel Desktop
Formulas & Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 25, 2018

Hi James,

 

That could be like

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($A2,$C$2:$C$16))),"here", "ERROR")

 

  • James Park's avatar
    James Park
    Copper Contributor
    Feb 25, 2018

    Hi Sergei,

     

    Thanks for the swift response! This perfectly meets my needs! Also I've seen it before but what is the purpose of the double dashes? 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 25, 2018

      James,

       

      Double dash converts logical value into its numeric equivalent (i.e. TRUE to 1 and FALSE to 0). You need it here since "product" part of SUMPRODUCT actually products nothing, you have only one array of logical values. And "sum" part, as SUM function, ignores text and logical values.

       

      Another option is to multiply that array on 1, when "product" of such arrays returns array of 1 and 0 values.

       

      Like this

      0	=SUM({TRUE,FALSE,TRUE})
2	=SUM(--{TRUE,FALSE,TRUE})
0	=SUMPRODUCT({TRUE,FALSE,TRUE})
2	=SUMPRODUCT(--{TRUE,FALSE,TRUE})
2	=SUMPRODUCT({TRUE,FALSE,TRUE}*1)