Partial-Match (Wildcard) Lookup

All versionsWildcardsVLOOKUP/XLOOKUP

To look something up when you only have part of the value — “Cedar” should find “Cedar Inc.” — use wildcards. Wrapping the lookup value in * tells VLOOKUP or XLOOKUP to match on a fragment.

Quick formula: to find the first company containing E2 and return column B:

=VLOOKUP("*"&E2&"*", A2:B8, 2, FALSE)

The * wildcards around E2 mean “any text before and after,” so a fragment matches the full name.

Functions used (tap for the full reference guide):

VLOOKUP XLOOKUP

The example

Look up the partial name “Cedar.”

	A	B	D	E
1	Company	Owner	Search	Owner
2	Acme Corp	Ana	Cedar	Cy
3	Bolt LLC	Ben
4	Cedar Inc	Cy

The formula

The owner of the company containing “Cedar”:

=VLOOKUP("*"&E2&"*", A2:B4, 2, FALSE) // "*Cedar*" matches "Cedar Inc" → Cy

How it works

Wildcards turn a fragment into a match:

"*"&E2&"*" builds the search string "*Cedar*" — the asterisks mean any characters can surround the fragment.
VLOOKUP with FALSE (exact match) honors wildcards, so it finds “Cedar Inc.”
It returns the value from column 2 on that row — Cy.
Use ? to match exactly one unknown character, e.g. "A?me".

XLOOKUP needs wildcard mode on. Set match_mode to 2: =XLOOKUP("*"&E2&"*", A2:A8, B2:B8, , 2). Unlike VLOOKUP, XLOOKUP won’t use wildcards unless you ask.

Try it: interactive demo

Live demo

Type part of a company name.

Search (E2)

Owner:

Variations

XLOOKUP wildcard mode

Turn on match_mode 2:

=XLOOKUP("*"&E2&"*", A2:A8, B2:B8, , 2)

Starts with / ends with

Anchor the wildcard:

=VLOOKUP(E2&"*", A2:B8, 2, FALSE)

Match a literal asterisk

Escape it with a tilde:

=VLOOKUP("~*", A2:B8, 2, FALSE)

Pitfalls & errors

XLOOKUP ignores wildcards by default. You must set match_mode to 2; otherwise it looks for the literal text including the asterisks.

First match wins. If a fragment matches several rows, you get the first one. Narrow the fragment or add criteria.

VLOOKUP must use FALSE. Wildcards only work with exact-match mode; approximate mode (TRUE) ignores them.

Practice workbook

📊

Download the free Partial-Match (Wildcard) Lookup practice workbook

A company list with live VLOOKUP wildcard lookup, the XLOOKUP-mode and starts-with 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 lookup value in wildcards: =VLOOKUP("*"&E2&"*", A2:B8, 2, FALSE). The asterisks match any text around the fragment, so a partial name finds the full record.

How do I make XLOOKUP match partial text?

Set match_mode to 2 for wildcards: =XLOOKUP("*"&E2&"*", A2:A8, B2:B8, , 2). XLOOKUP doesn't use wildcards unless you enable them.

Why isn't my wildcard lookup working?

VLOOKUP must use FALSE (exact match) for wildcards to work, and XLOOKUP needs match_mode 2. In approximate mode, the asterisks are treated as literal characters.

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