VLOOKUP normally needs the whole cell to match. If you search for "Acme" and the list says "Acme Pty Ltd", you get #N/A. To do a partial match, "contains", starts-with or ends-with lookup, you add wildcards. This page shows the formulas, and where partial matching stops working.
Master list, D2:E5 (name, ID):
Initech, 101
Umbrella Corp, 102
Acme Pty Ltd, 103
Globex Corporation, 104
Search terms go in column A, starting at A2. Row 1 holds headers.
The asterisk * stands for any run of characters. Put one on each side of your search value:
=IF(A2="","",IFERROR(VLOOKUP("*"&A2&"*",$D$2:$E$100,2,FALSE),""))
The last argument must be FALSE (exact match mode). Wildcards only work in that mode. Matching ignores case.
With A2:A5 = acme, Globex, Wayne, Corp, the results are 103, 104, blank, 102. "Wayne" is not in the list, so IFERROR gives a blank. "Corp" appears in both Umbrella Corp and Globex Corporation, and VLOOKUP returns the first match from the top, so you get 102. It does not warn you that there were two.
The blank check at the start matters. An empty search cell becomes "**", which matches every non-empty cell and returns the first row.
Starts with, wildcard on the right only:
=IF(A2="","",IFERROR(VLOOKUP(A2&"*",$D$2:$E$100,2,FALSE),""))
Searching Umb, Corp, glo gives 102, blank, 104. "Corp" is blank because no name starts with it.
Ends with, wildcard on the left only:
=IF(A2="","",IFERROR(VLOOKUP("*"&A2,$D$2:$E$100,2,FALSE),""))
Searching Ltd, corp, Corporation gives 103, 102, 104.
There is also a single-character wildcard, the question mark. "Sm?th" matches Smith and Smyth.
XLOOKUP does the same thing with a clearer layout. The final argument, 2, switches on wildcard matching:
=IF(A2="","",XLOOKUP("*"&A2&"*",$D$2:$D$100,$E$2:$E$100,"",2))
Same results as Method 1. The fourth argument, "", is what you get when nothing matches, so no IFERROR is needed.
Sometimes the cell holds a long string, such as "Invoice Acme Pty Ltd Sydney", and the master list holds the short name. Wildcards cannot help here, because the wildcard goes on the lookup value, not on the list. Use SEARCH instead, which tests whether each list item appears inside your text:
=IF(A2="","",IFERROR(INDEX(FILTER($E$2:$E$5,ISNUMBER(SEARCH($D$2:$D$5,A2))),1),""))
SEARCH returns a position if the list name is found inside A2, and an error if not. ISNUMBER turns that into TRUE or FALSE, FILTER keeps the matching IDs, and INDEX takes the first. Limit the range to your real data (D2:D5, not D2:D100): a blank cell in the list would count as found everywhere.
On the sample, "Invoice Acme Pty Ltd Sydney" gives 103, "Umbrella Corp refund" gives 102, and "Unknown supplier" gives blank.
If your search value itself has an asterisk or question mark, it is treated as a wildcard. Put a tilde in front of each one to make it literal:
=IFERROR(VLOOKUP("*"®EXREPLACE(A2,"([*?~])","~$1")&"*",$D$2:$E$100,2,FALSE),"")
Suppose the list has "AxB Ltd" (201) and "A*B Ltd" (202). Searching for A*B without the escape matches AxB Ltd first and returns 201. With the escape it returns 202.
Wildcards match text cells. A lookup column of true numbers (101, 102) will not match "*10*". Convert it with a helper column such as =TEXT(D2,"0").
Partial matching returns only the first hit, in sheet order. Short search terms like "Corp" give risky results. Sort or lengthen your terms if that matters.
It cannot handle typos, different word order or abbreviations. "Inytech" does not contain "Initech", and "Corp Umbrella" does not contain "Umbrella Corp". Wildcards only handle the case where one string is literally inside the other.
Extra spaces and punctuation break matches. Run Data > Data cleanup > Trim whitespace first.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
If your problem is typos, word order or punctuation rather than "one string inside another", the formulas above will not find the match. Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. It adds FUZZYLOOKUP(values, lookup, return_range, [threshold]), a fuzzy XLOOKUP, and FUZZYMATCH and FUZZYSCORE. You paste the functions into your sheet once, or copy a template sheet, so it works where a Workspace admin blocks add-ons. You own the code and it runs only inside your sheet. There is a 30-day refund if it does not work for you. For a one-off lookup with clean data, the free wildcard formulas above are enough and you do not need to buy anything.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.