How to How to Use MATCH with Wildcards in Excel in Excel - Practical Guide

Learn to use MATCH with wildcards to find approximate text matches in Excel. This tutorial covers wildcard characters (*, ?) and their integration with MATCH to locate partial text strings, enabling flexible lookups beyond exact matches for data analysis and reporting.

Why This Matters

Wildcard matching is essential for real-world data where exact matches are unreliable, allowing you to build robust formulas that handle spelling variations, partial names, and incomplete data.

Prerequisites

•Basic knowledge of MATCH function syntax
•Understanding of Excel cell references (absolute and relative)
•Familiarity with the IF and INDEX functions

Step-by-Step Instructions

Understand wildcard characters

Recognize that * matches any sequence of characters and ? matches any single character. For example, "Smith*" finds "Smith", "Smithson", and "*data*" finds any cell containing "data".

Set up your data range

Organize your lookup data in a single column or row. Click on the first cell of your search range (e.g., A1) and note the range extent where you want MATCH to search.

Enter the MATCH formula with wildcards

Click the cell where you want results and type: =MATCH("*text*",A:A,0). Replace "*text*" with your wildcard pattern and A:A with your actual range. The 0 parameter ensures exact matching (including wildcards).

Combine MATCH with INDEX for practical use

Use =INDEX(B:B,MATCH("*search*",A:A,0)) to return the corresponding value from column B when MATCH finds the wildcard match in column A. Press Enter to execute.

Test and validate results

Verify the formula returns the correct row number or value by checking against your data. Adjust the wildcard pattern if needed and use Ctrl+Shift+F9 to recalculate if results seem outdated.

Alternative Methods

Using FILTER function (Excel 365)

Use =FILTER(data, ISNUMBER(SEARCH("pattern", range))) to filter rows matching wildcard patterns directly without INDEX/MATCH.

Using SEARCH instead of MATCH

Combine SEARCH with IF and SMALL functions: =IFERROR(INDEX(range, SMALL(IF(ISNUMBER(SEARCH(pattern, range)), ROW(range)), ROW())), "") for array formulas.

Tips & Tricks

✓Use "*" at the beginning and end of your pattern ("*target*") for substring matching rather than prefix/suffix matching.
✓MATCH with wildcards only works with the third parameter set to 0 (FALSE); other values ignore wildcard characters.
✓Combine with IFERROR to handle cases where no match is found: =IFERROR(MATCH(...), "Not Found").
✓Remember that wildcards in Excel are case-insensitive; "smith*" and "SMITH*" return the same results.

Pro Tips

★Use ? for partial matching when you know the exact length: "A?2023" finds "AB2023", "AC2023", etc.
★Nest MATCH with multiple criteria using helper columns with CONCATENATE to search across multiple columns simultaneously.
★Escape literal asterisks and question marks using tilde (~): MATCH("price*",range,0) finds "price*" as literal text when written as "price~*".
★Use AGGREGATE with MATCH in combination to ignore error values in your lookup range automatically.

Troubleshooting

MATCH returns #N/A error with wildcards

Ensure the third parameter is 0 (exact match with wildcards), not 1 or -1. Also verify your wildcard pattern actually exists in the range by using SEARCH independently.

Wildcard formula works sometimes but not consistently

Check for leading/trailing spaces in your data using TRIM function: =MATCH("*pattern*",TRIM(A:A),0). Also confirm all cells in the range have consistent formatting.

Formula finds first match but you need a different occurrence

Use AGGREGATE with SMALL and SEARCH: =AGGREGATE(15,6,ROW(range)/(ISNUMBER(SEARCH(pattern,range))),n) where n is the occurrence number you want.

Wildcard pattern with special characters (parentheses, brackets) fails

Escape each special character with tilde (~): use "(text~)" to search for literal "(text)" in your data.

Related Excel Formulas

INDEX SEARCH FILTER COUNTIF SUMIF IF IFERROR

Frequently Asked Questions

Can MATCH use wildcards with numeric data?

No, wildcards only work with text. Convert numbers to text using TEXT function first, or use APPROXIMATE match (match_type 1 or -1) for numeric ranges instead.

What is the difference between ? and * in wildcard matching?

* matches zero or more characters ("A*B" finds "AB", "AXB", "AXXXB"), while ? matches exactly one character ("A?B" finds only "AXB", not "AB" or "AXXB").

Can I use MATCH wildcards in conditional statements like IF or COUNTIF?

COUNTIF and SUMIF natively support wildcards, but MATCH requires explicit wildcard syntax. For conditional logic, combine MATCH with IF or use COUNTIF directly for counting matches.

Does MATCH with wildcards work in all Excel versions?

Yes, wildcard support in MATCH (with match_type 0) is available in Excel 2000 and later, including all modern versions and Excel 365.

How do I search for a literal tilde (~) character in my data?

Escape the tilde as ~~: use MATCH("~~~~~",range,0) if your data contains actual tildes you want to find.