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

[mkpflorida]

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

 

[Fluff]

Hi & welcome to MrExcel.
If you want exact matches, try

=COUNTIFS(Real!$A$2:$A$7390,A2)

 

[mkpflorida]

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

 

[Fluff]

In what way?

 

[mkpflorida]

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]

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]

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]

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]

Are you actually using this in Sheets, or in Excel?