Advanced Filter, Exact Criteria

[Mojave]

I was working with the Advanced filter using a 200+ row list of criteria, and I kept getting a few extra model numbers here and there. As far as I could tell, I wasn't asking for them, but they kept showing up. Then I realized, the Excel Criteria function is not an exact science.

For instance: If you are looking for the city of "Johnson", I would type in Johnson for my criteria. But Excel would return Johnson, Johnson Valley, Johnsons Crossing, Johnson City etc.

Apparently Excel is in a default mode of "Starts with" when it does criteria. After beating my head against a dead horse, I finally realized the solution is rather easy. Put an equals sign in front of the criteria, so now it reads as

=Johnson

Of course, you have to throw in an apostrophe (') to make that work. So in the cell it reads:

'=Johnson

A little bit of text concats and I can now look up endless lists of stuff and get an exact match.

[MarvinP]

Hi Mojave and thanks for solving your own problem,

Please look at this page
http://www.excelfunctions.net/ExcelAdvancedFilter.html
to see if it explains what you discovered. If not, let us know.

I too have problems with Advanced Filters and try to use them all the time.

[Mojave]

Marvin that's a great page. That's what I was talking about and I just learned about 4 things! Thanks.

[domenechn1]

Thank you so much Mojave for sharing this , I was having the same issue