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]

[TABLE="class: grid"]
<tbody>[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]
</tbody>[/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?

That use case returns TRUE. I am still looking for one that returns TRUE if you were to delete A2 "red".

 

[Aladin Akyurek]

That use case returns TRUE. I am still looking for one that returns TRUE if you were to delete A2 "red".

It looks like you are changing your requirements: A2 is deleted; C1 specifies red; you want the formula to deliver TRUE, right? If so, is it because C1 partially match reds in A1?

 

[samgil]

It looks like you are changing your requirements: A2 is deleted; C1 specifies red; you want the formula to deliver TRUE, right? If so, is it because C1 partially match reds in A1?

Yep because C1 partially matches reds in A1. Apologies for the confusion, my initial question was answered (exact match), so I'd then asked for a partial match version in the 5th post.

 

[Aladin Akyurek]

Yep because C1 partially matches reds in A1. Apologies for the confusion, my initial question was answered (exact match), so I'd then asked for a partial match version in the 5th post.

For non-partial match (case-insensitive exact match)...

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

For case insensitive partial match, i.e. your current request...

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