Function Problems

Why COUNTIF Handles Escaped Tildes Differently from MATCH in Excel

Adam Davis Sep 30, 2026 869 views

Question details

Users want to know why the COUNTIF and MATCH/XMATCH functions in Excel evaluate escaped tildes and wildcards inconsistently.

Why COUNTIF Handles Escaped Tildes Differently from MATCH in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using wildcard characters and escaped tildes in lookup and counting formulas to search for specific text strings.
Observed behavior: COUNTIF treats a tilde as an escape character only when a wildcard is present, whereas MATCH requires the tilde to be escaped even when no wildcard exists in the string.

Before you start

Ensure you understand that the tilde (~) is used in Excel as an escape character to find literal asterisks (*), question marks (?), or literal tildes (~).

Solution 1Recommended

Adapt Your Formulas to Function-Specific Escape Rules

Learn the specific rules for how COUNTIF and MATCH parse escape characters so you can adjust your formulas accordingly without errors.

In Excel, the COUNTIF and MATCH functions use distinct logic for processing the tilde character. COUNTIF selectively triggers the escape behavior only when wildcard characters (* or ?) are also present in the criteria string.

Conversely, MATCH and XMATCH enforce escape behavior strictly. They require tildes to be escaped (~~) regardless of whether other wildcards exist in the string. This behavior is consistent across all modern Excel versions and appears to be a deliberate, albeit poorly documented, implementation difference.

Identify the function's specific rules

Recognize that COUNTIF only triggers escape behavior when a wildcard (* or ?) is present in the criteria, whereas MATCH strictly evaluates tildes as escapes regardless of wildcards.

Format MATCH criteria with double tildes

When using MATCH or XMATCH, always manually enter a double tilde (~~) if you intend to match a literal tilde in your dataset.

Use the SUBSTITUTE function for dynamic data

If referencing a cell that might contain literal tildes, wrap the reference in a SUBSTITUTE function inside your MATCH formula (e.g., =MATCH(SUBSTITUTE(A1, "~", "~~"), B:B, 0)) to automatically handle the escape character.

Adapt Your Formulas to Function-Specific Escape Rules

Pro Tip: Using the SUBSTITUTE function is the safest workaround when working with dynamic datasets, as it ensures all literal tildes are properly escaped before the MATCH function processes them.

Solution 2

Submit Feedback to Microsoft for Standardized Documentation

Report the confusing behavior to Microsoft to advocate for standardized documentation or unified formula logic.

Free Microsoft Office alternative

Experience Reliable Formula Processing with WPS Office

If you are frustrated by inconsistent formula behaviors and unhelpful documentation in Microsoft Office, try WPS Office. It provides a lightweight, highly compatible alternative for all your spreadsheet tasks with a familiar interface.

1. Download the software: Visit the official WPS Office website and click the free download button.
2. Install WPS Office: Run the lightweight installer and follow the on-screen prompts to complete the setup.
3. Open your Excel files: Launch WPS Spreadsheet and open your existing .xlsx files directly. All your formulas and data will load seamlessly.

Fully compatible with Microsoft Excel file formats (XLSX, XLS, CSV)Supports a comprehensive library of advanced spreadsheet functionsLightweight design with a familiar, easy-to-navigate interfaceFree to use with seamless migration of your existing workbooks

microsoft office alternative - wps office

Frequently Asked Questions

What does a tilde (~) do in Excel formulas?

A tilde is used as an escape character in certain Excel functions (like COUNTIF, MATCH, and SEARCH). It tells Excel to treat the next character as a literal character rather than a wildcard. For example, using ~* searches for a literal asterisk instead of any sequence of characters.

How do I search for a literal tilde in an Excel lookup?

To search for a literal tilde, you must escape it with another tilde. You should use a double tilde (~~) in your criteria string so the formula engine recognizes it as a standard character.

Does XMATCH fix the tilde escape behavior?

No. XMATCH uses the same underlying escape behavior logic as the traditional MATCH function, meaning it still handles escaped tildes differently than COUNTIF and requires tildes to be escaped even without wildcards.

Excel & Data Problems