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

Test.xlsx9 KB

Excel Desktop

Formulas & Functions

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

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)

Forum Discussion

searching values in part of another column and/or cell