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

 

(ignore the fact that the figures are in sequence, in reality they will be scattered all over, and ignore my poor lookup attempt)

I want to be able to search any value in column A that's embedded in column C -- and if the values are not there in the column, I want it to return an error message. 

 

Please let me know if you're able to understand exactly what I'm seeking.. and/or if you need additional clarity.

 

Thank you for all the assistance!

Excel Desktop
Formulas & Functions

3 Replies

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