How to Partial Match With Vlookup in Excel

Why This Task Matters in Excel

Imagine you receive a weekly product inventory list from a supplier. Their description field contains extra marketing phrases such as “NEW!”, “Limited Edition”, or “Bulk Pack” tacked onto the actual product name. Your internal master list, however, keeps descriptions clean and concise. When you need to reconcile the two lists, an exact match lookup fails because “Coffee Beans” is not identical to “Coffee Beans – Limited Edition NEW!”.

Partial matching solves this disconnect by allowing you to search for records that contain, start with, or end with a specific string rather than matching the entire cell. This is invaluable for:

• Sales reporting where customer names may include prefixes or suffixes (e.g., “Ms. Amy Chen – VIP”).
• Customer support logs in which ticket IDs embed prefixes like “INC-” or “REQ-”.
• Marketing analysis of social-media export files where handles often include emojis or hashtags.
• Finance reconciliations when vendor or invoice numbers are padded with leading zeros or year codes.

Across industries—retail, manufacturing, healthcare, and tech—data arrives from multiple sources. Rarely do those sources use the same naming conventions. Mastering partial lookups means you can merge, audit, and clean that data quickly without tedious manual edits. Failing to learn this skill leaves teams spending hours on drag-and-drop comparisons, copy-pasting, or even worse, accepting inaccurate merges that lead to reporting errors, inventory mistakes, or lost revenue.

Excel remains one of the most accessible data wrangling tools: almost every office has it, and you can build repeatable, auditable solutions without writing traditional code. Functions like VLOOKUP, XLOOKUP, and INDEX/MATCH combined with wildcard characters, SEARCH, or regular expressions (in Office 365) unlock flexible matching logic that used to require complex database queries. Partial matching is therefore both a foundational and gateway skill: once users see how wildcards empower VLOOKUP, they gain confidence to explore more advanced lookup strategies, text functions, and dynamic array formulas, weaving them into broader workflows such as Power Query, PivotTables, or Power BI.

Best Excel Approach

The most dependable way to perform a partial match with “classic” Excel functions is to combine VLOOKUP with the asterisk wildcard. When the fourth argument of VLOOKUP is FALSE (exact match), Excel still honors wildcard characters in the lookup value. By concatenating the wildcard to the lookup value, you instruct Excel to treat missing characters before, after, or around the search text as “anything goes”.

Core syntax:

=VLOOKUP("*"&lookup_value&"*", table_array, col_index_num, FALSE)

Why it is the best first choice:

• Backward compatibility—works in Excel 2007 through Microsoft 365.
• Users already comfortable with VLOOKUP only need to add two ampersands and two asterisks.
• Fast enough for moderate datasets (a few thousand rows) without complicated helper columns.

Use this method when you must look for a substring anywhere in the target column and you are required to support colleagues on older Excel versions. Switch to XLOOKUP or INDEX/MATCH when you need left-side lookups, multiple criteria, or dynamic array spill behavior.

Alternative syntax (begins with or ends with):

=VLOOKUP(lookup_value&"*", table_array, col_index_num, FALSE)   'Starts with
=VLOOKUP("*"&lookup_value, table_array, col_index_num, FALSE)   'Ends with

These targeted variations tighten the match pattern if you know the text’s position relative to the full string.

Parameters and Inputs

• lookup_value – Text or cell reference that contains the portion you want to find. Accepts numbers stored as text as well.
• table_array – The full lookup table including the column where Excel will search and the column(s) from which you want to return results. Example: [A2:D5000].
• col_index_num – A positive integer indicating which column inside table_array should be returned. Column 1 is the leftmost column of the table_array.
• range_lookup – Must be FALSE (or 0) for wildcard matching. TRUE triggers approximate match and ignores wildcards.

Data preparation rules:

1. The search column in table_array cannot contain true blank cells between data because VLOOKUP stops at the first empty row in an approximate context. While we use exact-match mode here, empty rows can still confuse users, so keep lists contiguous.
2. Ensure lookup_value is trimmed of leading/trailing spaces. Use TRIM or CLEAN if data is imported from systems that insert non-printing characters.
3. Case sensitivity: VLOOKUP is case-insensitive. If your business rule requires case-sensitive partial matching, switch to INDEX/MATCH with EXACT or to newer FILTER functions.
4. Non-text numbers: convert them to text via TEXT or the ampersand trick (“”&A2) before concatenating wildcards if you need substring matching within numeric IDs.
   Edge cases: If multiple rows satisfy the same partial criteria, VLOOKUP returns the first match it encounters from top to bottom. Sort priority columns accordingly or use an advanced approach to return all matches.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose column A contains “Product Description” in rows A2:A10, and column B holds each item’s unit price. Your master SKU list (another sheet) only stores the core product name. You want to pull the price even when the supplier pads descriptions.

Sample data on sheet [Supplier_List]:

On sheet [Master_List], cell A2 contains “Chocolate Bar”. In B2 you want its price.

Steps

1. Activate cell B2 in [Master_List].
2. Enter:

=VLOOKUP("*"&A2&"*", 'Supplier_List'!$A$2:$B$5, 2, FALSE)

3. Press Enter. Result: 1.20.

Why it works: Adding “*” before and after A2 tells Excel to accept any characters before and after “Chocolate Bar”. VLOOKUP scans the first column of table_array for the first cell containing that sequence, then returns column 2 (Price).

Variations

• If you know prefixes exist but not suffixes, drop the leading asterisk.
• If multiple “Chocolate Bar” sizes exist, sort the supplier list by size to control which price VLOOKUP finds first.

Troubleshooting

• #N/A? Confirm the product substring truly appears in the supplier text. Use FIND to test.
• Wrong price? There are probably multiple matches; ensure the desired row sits above others or add additional criteria (see Example 3).

Example 2: Real-World Application

Scenario: A service desk exports daily ticket data with IDs such as “INC-2023-00456”, “INC-2023-00457”, while your KPI dashboard stores just the numeric sequence “00456”. You need to pull the ticket’s status and owner to measure resolution time.

Data: Sheet [Tickets] columns A:C.

Dashboard sheet has numeric IDs in column D. In E2 (Status) and F2 (Owner):

=VLOOKUP("*"&D2&"*", Tickets!$A$2:$C$4, 2, FALSE)
=VLOOKUP("*"&D2&"*", Tickets!$A$2:$C$4, 3, FALSE)

Explanation: We clamp the wildcard to the left and right of D2 because “00456” could appear anywhere within the full ticket string (prefix may be INC or REQ, and the hyphen positions can vary across years).

Business impact: By automating these lookups, the dashboard computes key metrics without manual editing. Service managers therefore see updated lead times within minutes of data export, supporting faster resource allocation and SLA compliance.

Integration tips

• Combine with conditional formatting to highlight overdue tickets in red once status equals “In Progress” for over 3 days.
• When Tickets grows beyond 10,000 rows, convert it to an Excel Table named “tblTickets”. Replace hard ranges with structured references to improve formula readability and automatic table expansion:

=VLOOKUP("*"&[@ID]&"*", tblTickets, 2, FALSE)

Performance Considerations: Wildcards disable VLOOKUP’s binary search. Each formula scans top to bottom until it finds the first partial match. For 50,000+ records, consider INDEX/MATCH with calculated helper columns or move data to Power Query.

Example 3: Advanced Technique

Problem: Marketing wants every campaign code that contains “SUMMER23” returned—not just the first one. They also need associated spend figures. You therefore need an array solution that spills multiple lines.

Modern Excel (Microsoft 365) offers XLOOKUP with the optional match_mode 2 (wildcard) and the ability to return arrays. Combine it with FILTER for even more control.

Data on [Campaigns]:

Goal: List all SUMMER23 campaigns and the spend directly beneath a search box.

1. In [Dashboard] cell B1 type the search key “SUMMER23”.
2. In B4 enter:

=FILTER(Campaigns!A2:B5, ISNUMBER(SEARCH(B1, Campaigns!A2:A5)))

3. Press Enter. The formula spills both columns for every match.

Why mention this in a VLOOKUP tutorial? Because many organizations run a hybrid environment. You may need to support power users on Microsoft 365 while still publishing files to colleagues on Excel 2013. Understanding where VLOOKUP ends and new dynamic array functions begin helps you design forward-compatible workbooks.

Edge case handling

• Wrap FILTER with IFERROR to catch “no matches” messages:

=IFERROR(FILTER(Campaigns!A2:B5, ISNUMBER(SEARCH(B1, Campaigns!A2:A5))), "No campaigns found")

• For left-hand search columns, XLOOKUP solves what VLOOKUP cannot:

=XLOOKUP("*"&B1&"*", Campaigns!A2:A5, Campaigns!B2:B5, "Not found", 2)

Tips and Best Practices

1. Convert lookup tables to official Excel Tables (Ctrl+T). Formulas auto-expand when new rows arrive, preventing broken references.
2. Keep the search column far left within the table_array to avoid accidental column index miscounts.
3. Store wildcards in a helper cell if you need flexible patterns. Example: in cell Z1 type \"\"&A2&\"\", then reference Z1 in multiple VLOOKUPs. Changing the wildcard shape later requires only one edit.
4. Combine TRIM, CLEAN, and UPPER/LOWER within lookup_value to normalize messy text for more predictable matching.
5. If duplicate partial matches are likely, add a second criterion (e.g., quantity or region) with INDEX/MATCH or FILTER instead of trusting the first result.
6. Benchmark performance: use the Calculation Options menu to switch to “Manual” mode before copying thousands of wildcard formulas, then switch back to “Automatic” when done to avoid freezes.

Common Mistakes to Avoid

1. Forgetting FALSE in the fourth argument. Without it, VLOOKUP shifts to approximate mode, ignores your wildcards, and returns unpredictable matches.
2. Concatenating wildcards incorrectly. *"&A2&"* (missing opening quote) yields a #NAME? error. Always surround asterisks with quotes: \"\"&A2&\"\".
3. Expecting partial matches on numeric data when cells are genuinely numeric. Convert to text first or apply TEXT(A2,\"0\"). Wildcards only work on text strings.
4. Relying on VLOOKUP when the substring appears to the right of the return column. VLOOKUP cannot look left. Use INDEX/MATCH or XLOOKUP instead.
5. Overlooking hidden spaces or non-printing characters in the lookup column. Use LEN to inspect cell length; if results differ from visible characters, clean the data.

When errors occur, apply a systematic correction path: check FALSE parameter, verify wildcard placement, confirm text vs numeric, inspect hidden characters, and finally evaluate left-side limitations.

Alternative Methods

Choose INDEX/MATCH when you need to look left but remain compatible with pre-365 Excel. Opt for XLOOKUP to simplify syntax, especially when you also need error handling or want to return entire rows. FILTER is unbeatable for spilling multiple hits with minimal effort but is limited to modern Excel. When datasets exceed 100,000 rows, Power Query’s “Merge Queries” with “Fuzzy Matching” or “Contains” join delivers superior performance and keeps the workbook calculation engine light.

Migrating between methods: you can wrap existing VLOOKUP inside IFERROR or SWITCH to maintain legacy behavior while testing INDEX/MATCH or XLOOKUP in parallel. Once validated, retire VLOOKUP to reduce duplication.

FAQ

When should I use this approach?

Use VLOOKUP with wildcards when you need a quick, backward-compatible way to match a substring against a single column and only require the first matching result.

Can this work across multiple sheets?

Yes. Qualify table_array with the sheet name, as shown in examples. You can even refer to closed workbooks, but ensure they are accessible; otherwise VLOOKUP returns #REF!.

What are the limitations?

Wildcards only work in the lookup_value argument, not inside the table_array. VLOOKUP still cannot search left, and it stops at the first match—no duplicates returned. It is also case-insensitive.

How do I handle errors?

Wrap your formula in IFERROR or IFNA to display friendly text or perform fallback logic. Also pre-test with COUNTIF(\"\"&lookup&\"\",search_range) to confirm at least one match exists before executing the lookup.

Does this work in older Excel versions?

Yes, the wildcard technique functions as far back as Excel 2003, though structured tables are unavailable before 2007. In Excel 2003 you must use plain ranges.

What about performance with large datasets?

Wildcard lookups scan every row sequentially until they find the first match, so performance degrades linearly. For 100,000+ rows, filter data first, use INDEX with a helper column, or offload to Power Query. Consider turning off automatic calculation during heavy edits.

Conclusion

Partial matching with VLOOKUP is a straightforward yet powerful skill that bridges the gap between messy real-world data and the neat, standardized lists your analytics depend on. By mastering wildcard syntax, understanding input requirements, and knowing when to pivot to modern functions, you future-proof your workbooks and accelerate daily tasks from reconciliations to dynamic dashboards. Keep practicing with increasingly complex scenarios, experiment with INDEX/MATCH and XLOOKUP, and you will soon transform Excel into a flexible, database-like engine for any matching challenge.