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]

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

Yep apologies for the confusion. Both are acting as an exact match solution. E.g. if I have "reds" in a1, random text in A2:A10, and then "red" as my search in C1.

 

[Aladin Akyurek]

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.

What is the answer to the above question before we tackle this one?

 

[samgil]

What is the answer to the above question before we tackle this one?

Use Case 1 - exact match, all fields not null:
If A1 = red
A2:A10 = blue

And C1 = red

Then

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns TRUE - correct

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns TRUE - correct

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0
returns TRUE - correct

=SUMPRODUCT(--ISNUMBER(SEARCH(","&C1&",",","&SUBSTITUTE(A1:A10," ","")&",")))
returns TRUE - correct

=SUMPRODUCT(--(A1:A10=C1))>0
returns TRUE - correct


Use Case 2 - exact match, some fields null:

If A1:A9 = blue
A10 = null

And C1 = red

Then

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns FALSE - correct

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns FALSE - correct

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0
returns TRUE - incorrect

=SUMPRODUCT(--ISNUMBER(SEARCH(","&C1&",",","&SUBSTITUTE(A1:A10," ","")&",")))
returns FALSE - correct

=SUMPRODUCT(--(A1:A10=C1))>0
returns FALSE - correct

Use Case 3 - contains match, some fields null

If A1 = reds
A2:A9 = blue
A10 = null

And C1 = red

Then

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns FALSE - incorrect

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0
returns FALSE - incorrect

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0
returns TRUE - correct

=SUMPRODUCT(--ISNUMBER(SEARCH(","&C1&",",","&SUBSTITUTE(A1:A10," ","")&",")))
returns FALSE - incorrect

=SUMPRODUCT(--(A1:A10=C1))>0
returns FALSE - incorrect

 

[Aladin Akyurek]

Would you please just answer the question I posed?

 

[samgil]

Would you please just answer the question I posed?

reds, blues, greens

and C1 = red

Want it to return FALSE



if a4 = RED, what do we get?


We get TRUE.

 

[Aladin Akyurek]

reds, blues, greens

and C1 = red

Want it to return FALSE



if a4 = RED, what do we get?


We get TRUE.

Try:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&C1&" "," "&$A$1:$A$10&" ")))

This delivers TRUE if C1 is in A1:A10 in a stand-alone manner.

It will miss red when an A-cell contains for example:

it's here red, not blue

 

[samgil]

Try:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&C1&" "," "&$A$1:$A$10&" ")))

This delivers TRUE if C1 is in A1:A10 in a stand-alone manner.

It will miss red when an A-cell contains for example:

it's here red, not blue

Unfortunately that doesn't work for contains or exact match. Really appreciate the help so far though!

 

[Aladin Akyurek]

Unfortunately that doesn't work for contains or exact match. Really appreciate the help so far though!

Care to post for what it doesn't work?

 

[samgil]

Care to post for what it doesn't work?

A1 = reds
A2 = red
A3:A10 = blue

C1 = red

doesn't work for that sorry.

 

[Aladin Akyurek]

A1 = reds
A2 = red
A3:A10 = blue

C1 = red

doesn't work for that sorry.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]reds[/td][td][/td][td]red[/td][/tr]
[tr][td]

2​

[/td][td]red[/td][td][/td][td]

TRUE​

[/td][/tr]
[tr][td]

3​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]blue[/td][td][/td][td][/td][/tr]
[/table]

In C1 enter:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&C1&" "," "&$A$1:$A$10&" ")))

If your system fails to deliver TRUE in C2, what happens? Error, a wrong result?