Lookup with Wildcards (Partial Match)

All versionsWildcards

Match on part of a value — “starts with,” “contains” — using wildcards. * stands for any characters and ? for a single one, in VLOOKUP or XLOOKUP.

Quick formula: find the first entry containing the text in E2:

=VLOOKUP("*"&E2&"*", table, 2, FALSE)

Wrapping the term in * wildcards matches it anywhere in the lookup value. FALSE keeps it an exact (wildcard) match.

Functions used (tap for the full reference guide):

VLOOKUP XLOOKUP

The example

Search “ace” and match “Acme Inc”.

	A	B
1	Search	Match
2	acme	Acme Inc — $4,200

The formula

Wrap the term in wildcards:

=VLOOKUP("*"&E2&"*", names, 2, FALSE) // contains match

How it works

Wildcards turn a lookup into a pattern match:

* matches any run of characters; ? matches exactly one.
Concatenate them around the term: "*"&E2&"*" = “contains”; E2&"*" = “starts with.”
Keep the match type FALSE (VLOOKUP) or match mode 2 (XLOOKUP) to enable wildcards.
It returns the first wildcard match — order your data so the intended hit comes first.

XLOOKUP wildcards need match mode 2: =XLOOKUP("*"&E2&"*", names, vals, , 2). To match a literal * or ?, precede it with a tilde (~*).

Try it: interactive demo

Live demo

Type part of a company name.

Match:

Variations

Starts with

Wildcard only after:

=VLOOKUP(E2&"*", names, 2, FALSE)

XLOOKUP wildcard

Match mode 2:

=XLOOKUP("*"&E2&"*", names, vals, , 2)

Single character

? matches one char:

=VLOOKUP("A?me", names, 2, FALSE)

Pitfalls & errors

Wildcards need exact mode. Use FALSE in VLOOKUP or match mode 2 in XLOOKUP — approximate mode ignores wildcards.

First match only. A wildcard can match many rows; you get the first. Narrow the term or sort the data.

Literal * or ?. Escape with a tilde (~*) to match the actual character.

Practice workbook

📊

Download the free Lookup with Wildcards (Partial Match) practice workbook

A wildcard-lookup sheet with live VLOOKUP, the starts-with and XLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do a partial-match lookup in Excel?

Wrap the term in wildcards: =VLOOKUP("*"&E2&"*", table, 2, FALSE) matches the term anywhere. * is any characters, ? is one. Keep the match type FALSE.

How do I match values that start with a term?

Put the wildcard after: =VLOOKUP(E2&"*", table, 2, FALSE).

How do wildcards work with XLOOKUP?

Use match mode 2: =XLOOKUP("*"&E2&"*", lookup, return, , 2).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: VLOOKUP · XLOOKUP