SUMPRODUCT: Having a dynamic range of cells to search for

[samgil]

Hi Gang,

I want to change this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"A1","A2","A3"},C1)))>0

to be a range like A1:A10 instead of "a1", "a2", "a3". However, if none of the cells in the range A1:A10 match C1, yet are empty, it will still return TRUE for some reason.

 

[samgil]

Ah yes you are correct!

 

[samgil]

Care to post a few rows from a1:a10 and the value in c1?

reds, blues, greens

and C1 = red

Want it to return FALSE

 

[samgil]

Sorry the opposite. XOR LX's solution is an exact match, but not a 'contains' match.

 

[XOR LX]

Sorry the opposite. XOR LX's solution is an exact match, but not a 'contains' match.

But I already gave you the 'contains' match solution earlier!

Regards

 

[Aladin Akyurek]

reds, blues, greens

and C1 = red

Want it to return FALSE

if a4 = RED, what do we get?

 

[Marcelo Branco]

reds, blues, greens

and C1 = red

Want it to return FALSE

Maybe...
=SUMPRODUCT(--ISNUMBER(SEARCH(","&C1&",",","&SUBSTITUTE(A1:A10," ","")&",")))

M.

 

[samgil]

Maybe...
=SUMPRODUCT(--ISNUMBER(SEARCH(","&C1&",",","&SUBSTITUTE(A1:A10," ","")&",")))

M.

That also works for exact match, but not a contains search.

 

[samgil]

But I already gave you the 'contains' match solution earlier!

Regards

It seems to be an exact match solution

 

[samgil]

if a4 = RED, what do we get?

With this formula =SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0

Then it returns TRUE for Red, which is great. However, even if I delete 'Red' from C1, it still shows TRUE, due to the empty cells in the range. If all the cells in the range are not blank, then it works.

 

[XOR LX]

It seems to be an exact match solution

Very confused now. In Post #4 I gave you a "contains" match solution; in post #8 an "exact" match solution.

Can you clarify which of those does not do as you wish?

Regards