Function Problems

How to Find a Part Number Within a Cell Text String Using VLOOKUP or INDEX MATCH

Natalie Taylor Oct 10, 2026 869 views

Question details

The user needs to retrieve a related identifier from another column by matching a specific part number that is mixed with other text strings inside a single lookup cell.

How to Find a Part Number Within a Cell Using Formulas

Product: Spreadsheets
Device & OS: not provided
Scenario: Performing a data lookup where the target lookup value is embedded within a string of multiple part numbers in the source dataset.
Observed behavior: Using a standard exact VLOOKUP formula fails and returns an error because the lookup cell contains extra text, preventing a direct exact match.

Before you start

Ensure that the part numbers in your dataset have consistent formatting and spacing to prevent formula errors or false partial matches during wildcard searches.

Solution 1Recommended

Use INDEX and MATCH with Wildcard Criteria

This is the most reliable method for finding a substring within a larger text string in a cell, especially when your return column is to the left of the lookup column.

By combining the INDEX and MATCH functions with wildcard characters (asterisks), you can instruct the spreadsheet to search for a specific sequence of characters anywhere within the target cell text.

Select the destination cell

Click on the empty cell where you want the related identifier to appear.

Input the INDEX and MATCH formula

Type the formula: =INDEX(Sheet1!A:A, MATCH("*"&C3&"*", Sheet1!B:B, 0)). Replace 'Sheet1!A:A' with your return column, 'C3' with your target part number, and 'Sheet1!B:B' with the column containing the multiple part numbers.

Execute the formula

Press Enter on your keyboard. The formula will search column B for any cell containing the part number in C3 and return the corresponding value from column A.

How Wildcards Work: The ampersand (&) is used to concatenate the asterisks (*) to your lookup value. The asterisk acts as a wildcard, meaning 'any number of characters before or after' the target value.

Solution 2

Use VLOOKUP with Wildcard Characters

If your return column is located to the right of your lookup column, you can use a standard VLOOKUP combined with wildcard characters.

Advanced Spreadsheet Functions

Easily Manage Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup formulas, including wildcard VLOOKUP and INDEX/MATCH combos, allowing you to seamlessly process complex text strings and manage large datasets efficiently.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your part numbers.
2. Navigate to the formula bar: Select the result cell and click on the formula bar at the top, or go to the Formulas tab to insert a function.
3. Enter your wildcard formula: Input your INDEX/MATCH or VLOOKUP formula using the asterisk wildcards (e.g., "*"&C3&"*").
4. Press Enter to calculate: Hit Enter to instantly retrieve the hidden part number data from the complex text string.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsIntuitive formula builder with syntax highlighting for error-free inputLightweight, fast, and free to use for daily data analysis tasksBuilt-in function templates to speed up complex calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why does my standard VLOOKUP return an #N/A error?

A standard VLOOKUP without wildcards searches for an exact cell match. If the lookup cell contains additional text, spaces, or multiple part numbers, the exact match fails and returns an #N/A error.

Can I use wildcard searches with the XLOOKUP function?

Yes. If your spreadsheet software supports XLOOKUP, you can use wildcards by setting the 'match_mode' argument (the fifth parameter) to 2. The formula would look like =XLOOKUP("*"&C3&"*", B:B, A:A, "Not Found", 2).

Are wildcard lookup formulas case-sensitive?

No, standard VLOOKUP and MATCH functions combined with wildcards are not case-sensitive. 'PartA' and 'parta' will be treated as the same string. If you need a case-sensitive search, you will have to use an array formula utilizing the EXACT and SEARCH functions.

What happens if multiple cells contain the same part number?

Both VLOOKUP and INDEX/MATCH will stop searching and return the result corresponding to the very first matching instance they encounter in the column, searching from top to bottom.

Excel & Data Problems