VLOOKUP Partial Match in Excel (wildcards)

The wildcards

• * — any number of characters.
• ? — exactly one character.

Contains a word

=VLOOKUP("*motors*",A2:C100,2,FALSE)

Finds the first company name containing “motors”. The last argument must be FALSE.

Using a cell

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

Starts with / ends with

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

XLOOKUP version

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"Not found",2)

Match mode 2 turns wildcards on. See XLOOKUP.

Watch out

• Only the first match is returned. For all of them: return multiple values.
• Wildcards work on text. A number column needs the numbers stored as text, or use FILTER(...,ISNUMBER(SEARCH(E2,A2:A100))).
• To search for a real * or ?, put ~ before it.

Still #N/A? VLOOKUP not working