# False positives when using IF(SUMPRODUCT(--ISNUMBER(SEARCH

#### mkpflorida

##### New Member
Hi Everyone:

I am brand new here and appreciate all the information here. I think I have an easy question, but cant seem to match an answer.

Here's my formula:

=SUMPRODUCT(--ISNUMBER((SEARCH(indirect("REAL!\$A\$2:\$A\$7390"),A2))))

What I am trying to do is conditionally highlight based upon the formula above any cell that exact matches from a list in another sheet named "REAL", which has a list of items in column A from 1 to row 7390.

The problem is I am getting false positives because the above formula is not finding exacts, but also partials. Here's an example:

I want to find if "yellow banana" is in the other list on the "REAL" sheet, which it not. But "Red Banana" is. My current formula still highlights the cell in my other sheet because it finds the word "banana".

Hopefully that makes sense.

Hopefully someone can provide suggestions.

Michael

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
If you want exact matches, try
Excel Formula:
=COUNTIFS(Real!\$A\$2:\$A\$7390,A2)

#### mkpflorida

##### New Member
Thanks for the reply, but that didn't work for me.

In what way?

#### mkpflorida

##### New Member
It didn't perform the action I wanted.

My formula is close, but it gives false positives.

Here's the formula again:
=SUMPRODUCT(--ISNUMBER((SEARCH(indirect("REAL!\$A\$2:\$A\$7390"),A2))))

I did a test worksheet again, just to put in a small sample size. it works for the most part, but one thing I noticed is that, using the examples above, that banana wasn't highlighted, but if I add the word "The" at the beginning such as, "The Purple Banana", that it will be highlighted, even though the exact phrase "The Purple Banana" is not in the other tab list, but "Purple Banana" is.

I'd like to keep the same formula with minor tweaks to make it find only the exact match, if possible.

Thanks again to anyone that can help!

#### Fluff

##### MrExcel MVP, Moderator
Can you post some actual sample data from both sheets, as the formula I suggested should do exactly what you are asking for.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### jasonb75

##### Well-known Member
I'd like to keep the same formula with minor tweaks to make it find only the exact match, if possible.
The formula you've used is not suitable for exact matches, the suggestion that @Fluff provided is the best one to use.

Use of INDIRECT is pointless, you don't need it.
Use of SEARCH only tells you if the search string is found in the cell, not if it is the whole cell or just part of it.
Without using SEARCH, ISNUMBER becomes pointless.

Taking the above out of the formula, you're left with SUMPRODUCT which is being used for a count.

COUNTIFS, which @Fluff suggested will do exactly the same thing as SUMPRODUCT but much more efficiently.

If it is not doing what you need then it is not the answer that is wrong, it is your question.

#### mkpflorida

##### New Member
I appreciate the feedback! I will try your suggestions and keep practicing. I'm sure what you are saying is correct, so I will retry.

Just curious, would same the countifs formula work in google sheets? Seems like most formulas translate over? I know it's an excel forum, but was just curious if any modifications need to be made if using google sheets.

Thanks again for the help. I will definitely try out the tool.

#### Fluff

##### MrExcel MVP, Moderator
Are you actually using this in Sheets, or in Excel?

Replies
14
Views
209
Replies
2
Views
227
Replies
4
Views
286
Replies
11
Views
405
Replies
5
Views
129

1,186,846
Messages
5,960,169
Members
438,463
Latest member
netobraga

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back