VLOOKUP With Wildcards: Partial Match Lookups

A VLOOKUP wildcard lets you find a row when you only know part of the value, such as "Harbor" instead of "Blue Harbor Foods". You add an asterisk before and after the search text, keep FALSE as the last argument, and VLOOKUP returns the first row that contains your text. This guide covers the three wildcard characters, contains, starts-with and ends-with lookups, and the cases where wildcards don't work.

New to VLOOKUP itself? Start with the VLOOKUP guide for beginners.

How to use a wildcard in VLOOKUP#

Join an asterisk to each side of the lookup value with the & sign. All the examples use this customer list:

With Harbor in F2, type:

=VLOOKUP("*"&F2&"*",A2:C6,2,FALSE)

The result is Ahmed Khan. The lookup value becomes *Harbor*, which means "any text, then Harbor, then any text". Only Blue Harbor Foods fits.

The FALSE at the end is essential. Wildcards only work with an exact match. With TRUE, VLOOKUP treats the asterisk as a normal character and the result is unpredictable. The guide to VLOOKUP exact match explains why FALSE matters in general.

The three wildcard characters#

Excel has three wildcard characters, and they work the same way in VLOOKUP, COUNTIF, SUMIF and MATCH:

The tilde is rarely needed, but it matters with product codes or notes that contain an asterisk or question mark. Without it, "A*" would match every value starting with A.

Contains, starts with and ends with#

Where you put the asterisk decides what kind of partial match you get:

"Ends with Red" fails because no company name ends in "Red". Starts-with lookups are safer than contains lookups, because they match fewer rows by accident.

The question mark is stricter than the asterisk, because it stands for exactly one character. =VLOOKUP("Summit Office C?",A2:C6,3,FALSE) returns Austin, because "Co" fills the pattern with one character after the C. "Summit Office Company" would not match, since it has more than one character there. Use ? when you know the length but not every letter, such as codes like "AB-1?".

You can also type the wildcard straight into the formula, such as =VLOOKUP("Summit*",A2:C6,3,FALSE), which returns Austin. Putting the search word in a cell is better, though, because you can change it without editing the formula.

VLOOKUP returns the first match only#

When more than one row matches, VLOOKUP returns the first one from the top. With F2 = "er", *er* matches both Northwind Traders and Redwood Paper Ltd. VLOOKUP returns Lisa Grant from row 2, even if you meant Redwood Paper. Short search words often match more than you expect, so check by counting first:

=COUNTIF(A2:A6,"*"&F2&"*")

If this returns more than 1, make the search word longer or more specific. To see every match instead of the first, use FILTER with SEARCH in Microsoft 365: =FILTER(B2:B6,ISNUMBER(SEARCH(F2,A2:A6))). COUNTIF and SUMIF wildcards follow the same rules, as the wildcard criteria practice shows.

Wildcards don't work on numbers#

Wildcards only match text. If column A holds order numbers stored as real numbers, such as 10452, then "*452*" won't find it, and VLOOKUP returns #N/A.

The fix is a helper column that turns the numbers into text. Insert a column to the left of the table and fill it with =B2&"" (where B holds the numbers), then look up in the new column. Joining an empty string converts the number to text without changing how it looks. Leave the original column as numbers, so sums and sorting still work, and use the helper column only for the lookup.

If your lookup still returns #N/A with text data, the cause is often a hidden space or a missing quote. Work through the common reasons VLOOKUP stops working.

Wildcards in XLOOKUP#

XLOOKUP also supports wildcards, but only when you set its fifth argument, match_mode, to 2:

=XLOOKUP("*"&F2&"*",A2:A6,B2:B6,"Not found",2)

With Harbor in F2, this returns Ahmed Khan, and it shows "Not found" instead of #N/A when nothing matches. Without the 2, XLOOKUP looks for a company literally called *Harbor*. XLOOKUP needs Excel 2021 or Microsoft 365.

Common mistakes with VLOOKUP wildcards#

1. Asterisks outside the quotes. =VLOOKUP(*&F2&*,...) is a syntax error. Each asterisk must be in double quotes: "*"&F2&"*".
2. Quotes around the cell. "*F2*" searches for the letters F2. Join the cell with &.
3. TRUE or a missing last argument. Wildcards need FALSE.
4. Searching numbers. Convert them to text first.
5. Too short a search word. Check how many rows match with COUNTIF.

If you need to return several columns for the matched row, the guide to VLOOKUP returning multiple columns shows how, and the column index number explained helps you pick the right one. The VLOOKUP function guide is a quick syntax reference.

Practise it#

Partial matches are easy once you've built a few lookup values by hand. Practise wildcards in criteria in a live spreadsheet: you type the formula and it checks the result instantly. Then practise VLOOKUP to combine wildcards with exact-match lookups.