Today’s question from Bill.
Did you know that Excel’s Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.

Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.

In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.

This all begs the question: Why am I trying to use Advanced Filter at all? How about IFERROR(SIGN(MATCH))) and filter that column to 1?

Table of Contents
(0:00) Advanced Filter Review
(0:50) Bug Filter returns partial matches
(1:18) Reading Excel Help
(1:35) Formula in Criteria
(2:09) 100K rows, 250 criteria phrases
(2:40) Build formula with ^ instead of =
(3:12) Replace ^ with =
(3:37) Use MATCH instead?