Cellwise Try it free

Excel guide · 4 min read

How to do a partial match lookup in Excel

Quick answerWrap the search text in asterisks: =VLOOKUP("*"&F2&"*",A2:C9,3,FALSE) finds the first row whose name contains the text in F2. With XLOOKUP, set match_mode to 2 so the asterisks act as wildcards: =XLOOKUP("*"&F2&"*",A2:A9,C2:C9,"Not found",2). Both return only the first match; use FILTER to get all of them.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHI
1ProductSKUPriceIn stockSearchKeywordCategory
2Oak Standing Desk 140cmDSK-14048912chairchairSeating
3Mesh Office ChairCHR-21022931deskdeskDesks
4Desk Lamp LEDLMP-05539.564lampLighting
5Ergonomic Chair ProCHR-3303498
6Monitor Arm DualARM-20411922
7Chair Mat 120x90MAT-1204540
8Cable Tray Under-DeskTRY-0182755
9Footrest AdjustableFTR-0073417

Download this sheet as an Excel practice file (.xlsx)

VLOOKUP with wildcards

An office-furniture price list has long product names, and people search with a single word like "chair". In a lookup, * stands for any number of characters and ? for exactly one. "*"&F2&"*" builds the pattern *chair*, which means "contains chair anywhere".

Wildcards only work in exact-match mode, so the last argument must be FALSE (or 0). The match ignores case. Use "chair*" for "starts with" and "*chair" for "ends with".

=VLOOKUP("*"&F2&"*",A2:C9,3,FALSE)

Result: 229

229, the price of Mesh Office Chair, the first name containing "chair".

XLOOKUP needs match_mode 2

XLOOKUP treats * as a literal asterisk unless you tell it otherwise. Leave out the fifth argument and it looks for a product actually named *chair*, finds none and returns the "Not found" text. Setting match_mode to 2 turns on wildcard matching.

XLOOKUP is available in Excel for Microsoft 365, Excel 2021 and later. A sixth argument of -1 searches from the bottom up, so =XLOOKUP("*"&F2&"*",A2:A9,C2:C9,"Not found",2,-1) returns the last chair instead: 45, the Chair Mat.

=XLOOKUP("*"&F2&"*",A2:A9,C2:C9,"Not found",2)

Result: 229

229. Without the 2 at the end, the same formula returns Not found.

INDEX and MATCH for any column

MATCH with match_type 0 also accepts wildcards, and it works in every version of Excel. Combine it with INDEX when the column you want is to the left of the one you search, or when you want a formula that survives inserted columns.

=INDEX(B2:B9,MATCH("*"&F3&"*",A2:A9,0))

Result: DSK-140

DSK-140, the SKU of Oak Standing Desk 140cm.

Only the first match comes back

"desk" appears in three product names: Oak Standing Desk, Desk Lamp LED and Cable Tray Under-Desk. Every lookup above silently returns the first and ignores the rest, and a search for "desk" probably did not mean a lamp. Before you trust a partial-match result, check how many rows match.

If the count is more than 1, either make the search text more specific or show all matches with FILTER (next section).

=COUNTIF(A2:A9,"*"&F3&"*")

Result: 3

3 rows contain "desk". For "chair" the count is also 3.

Return every match with FILTER and SEARCH

In Excel 365 and 2021, FILTER returns every row that passes a test. SEARCH(F2,A2:A9) gives the position of "chair" in each name, or #VALUE! where it is missing. ISNUMBER turns that into TRUE or FALSE, which is what FILTER needs. SEARCH ignores case; use FIND instead when case matters.

You can multiply in more conditions. =FILTER(A2:C9,ISNUMBER(SEARCH(F2,A2:A9))*(D2:D9>10),"No match") keeps only chairs with more than 10 in stock, which drops Ergonomic Chair Pro. To list the names in one cell, use =TEXTJOIN(", ",TRUE,FILTER(A2:A9,ISNUMBER(SEARCH(F2,A2:A9)))).

=FILTER(A2:C9,ISNUMBER(SEARCH(F2,A2:A9)),"No match")

Result: spills 3 rows × 3 columns

Mesh Office ChairCHR-210229
Ergonomic Chair ProCHR-330349
Chair Mat 120x90MAT-12045

Spills all three chairs with their SKU and price: Mesh Office Chair, Ergonomic Chair Pro and Chair Mat 120x90.

The reverse: the cell contains a keyword from a list

Sometimes the long text is what you look up, and the table holds the short keywords, like categorizing products or bank transaction descriptions by keyword. Wildcards cannot do this, because the pattern would have to come from the table. Search for every keyword inside the one cell and return the first that is found.

Order the keyword list from most to least specific, because the first hit wins. This needs Excel 365 or 2021 for XLOOKUP.

=XLOOKUP(TRUE,ISNUMBER(SEARCH(H2:H4,A8)),I2:I4,"Other")

Result: Desks

Desks: "Cable Tray Under-Desk" contains the keyword desk. Monitor Arm Dual in A6 would return Other.

Common mistakes

An empty search cell matches everything

If F2 is blank, "*"&F2&"*" becomes ** and matches the first row, and SEARCH("",...) matches every row. Wrap the formula in =IF(F2="","",...) so a blank search returns a blank.

Wildcards on a column of numbers

* and ? only match text. A partial search for 104 in a column of numeric IDs like 10452 returns #N/A. Search a text version of the IDs instead, for example a helper column with =B2&"".

Searching for a real * or ?

Put a tilde before it: "*~**" finds names containing an asterisk, and ~? finds a literal question mark.

VLOOKUP with TRUE (approximate match)

Wildcards are only supported in exact-match mode, and approximate mode expects sorted data, so the result is unreliable. Always use FALSE or 0 for a partial match.

FAQ

Can VLOOKUP do a partial match?

Yes. Use wildcards with exact match: =VLOOKUP("*"&F2&"*",A2:C9,3,FALSE) returns the first row whose lookup column contains the text in F2.

How do I use wildcards in XLOOKUP?

Set the fifth argument, match_mode, to 2: =XLOOKUP("*"&F2&"*",A2:A9,C2:C9,"Not found",2). Without it, XLOOKUP treats * as an ordinary character.

How do I return all partial matches, not just the first?

Use FILTER with ISNUMBER(SEARCH()): =FILTER(A2:C9,ISNUMBER(SEARCH(F2,A2:A9)),"No match"). It needs Excel 365 or 2021. In older versions you need a helper column that numbers the matches.

Is a partial match lookup case-sensitive?

No. Wildcards in VLOOKUP, XLOOKUP, MATCH and COUNTIF ignore case, and so does SEARCH. Use FIND inside FILTER when you need upper and lower case to count as different.

Functions used

Related guides

Updated