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

mkpflorida

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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.

Thanks in advance!

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
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
If you want exact matches, try
Excel Formula:
=COUNTIFS(Real!$A$2:$A$7390,A2)
 
Upvote 0

mkpflorida

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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!
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,473
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

mkpflorida

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,668
Office Version
  1. 365
Platform
  1. Windows
Are you actually using this in Sheets, or in Excel?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top