How to Perform a Partial Match Across Two Columns in Excel to Return a Value
Question details
The user needs a reliable method or formula to partially match text (such as slightly varying business names) across two columns and return a corresponding value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing or merging datasets where names or identifiers do not match exactly and require a partial or fuzzy search to extract related values.
- Observed behavior
- Standard exact-match formulas fail to retrieve the desired values when text formats or business names differ slightly between worksheets.
Clean your dataset by removing extra leading or trailing spaces using the TRIM function, and clearly identify the columns that contain the lookup text and the data you wish to return.
Use XLOOKUP with Wildcard Characters
The modern XLOOKUP function natively supports wildcard matching for partial text searches, making it the most flexible and reliable choice.
Wildcards like the asterisk (*) represent any sequence of characters. By wrapping your lookup value in asterisks, XLOOKUP will find the corresponding cell that contains your specific text string anywhere within it.
Click the empty cell where you want the returned value to appear.
Type `=XLOOKUP("*"&A2&"*", D:D, E:E, "Not Found", 2)`. In this example, A2 contains the partial text, D:D is the column to search, and E:E is the column containing the value to return.
Ensure the final argument is set to `2`. This specifically instructs the XLOOKUP function to perform a wildcard match rather than an exact match.
Press Enter to get the result. Click and drag the fill handle at the bottom right of the cell to apply the formula down the column.
Use VLOOKUP with Wildcard Characters
A traditional VLOOKUP formula combined with wildcards is highly effective and widely supported across older versions of Excel.
Use INDEX and MATCH for Left-Side Lookups
If the column containing your return value is located to the left of your lookup column, INDEX and MATCH with wildcards is the perfect workaround.
Perform Partial Matches Seamlessly with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced lookup functions like XLOOKUP, VLOOKUP, INDEX, and MATCH, including complete wildcard capabilities to easily handle partial matches and messy data.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your business names or data lists.
- 2. Type your formula: Click the target cell and enter your preferred wildcard lookup formula, such as `=VLOOKUP("*"&A2&"*", D:E, 2, FALSE)`.
- 3. Get the result: Press Enter to instantly retrieve the corresponding value based on the partial text match.
Frequently Asked Questions
Can I perform a partial match without using wildcard characters?
Yes, you can combine the SEARCH and ISNUMBER functions alongside FILTER or XLOOKUP. This method searches for a specific substring within an array and returns TRUE or FALSE, which is then used to filter or look up the desired return value.
Why is my VLOOKUP partial match returning an #N/A error?
The #N/A error usually occurs if the partial text does not exist at all in the lookup column, if there are hidden trailing spaces disrupting the match, or if the wildcards (*) were not correctly concatenated with ampersands (&) around the cell reference.
How do I handle partial matches with typos or misspellings?
Standard wildcard formulas look for exact substring matches. If business names have typos, you will need a 'fuzzy matching' approach. This can be done using the Fuzzy Merge feature found in Power Query, which assigns a similarity threshold to match slightly misspelled words.
Can I return multiple values for a partial match?
Yes. If a partial text string matches multiple rows, you can use the FILTER function combined with ISNUMBER and SEARCH to extract an array of all matching records instead of just the first occurrence.
