How to Lookup a Number Within a Cell Containing Multiple Values in Excel
Question details
The user needs to locate a specific six-digit number within target cells that contain multiple numbers and text strings, such as comma-separated values.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific numerical string that is embedded within a larger text string in a different column.
- Observed behavior
- Standard exact-match lookup functions like VLOOKUP fail to find the target number because it is mixed with other characters in the destination cell.
Ensure your numerical search data is formatted as text if it contains leading zeros, and identify whether you need to return a corresponding value from an adjacent column or simply verify the number's existence.
Use INDEX and MATCH with Wildcards
Utilize the MATCH function combined with asterisk wildcards to perform a partial text search within a range.
A standard MATCH function looks for an exact cell match. By wrapping your search criteria in asterisks (*), you instruct the function to look for the specific sequence of characters anywhere within the target cells, ignoring whatever text comes before or after it.
Click on the cell where you want the lookup result to be displayed.
Type the formula: =INDEX(ReturnRange, MATCH("*"&SearchValue&"*", LookupRange, 0)) into the formula bar.
Replace 'ReturnRange' with the column containing your desired output, 'SearchValue' with the cell reference of your 6-digit number, and 'LookupRange' with the column containing the comma-separated data.
Press Enter to execute the lookup. You can then drag the fill handle down to apply this formula to the rest of your column.

Use XLOOKUP with Wildcard Match Mode
For modern spreadsheet users, XLOOKUP provides a built-in wildcard match mode specifically designed for partial text searches.
Perform Advanced Partial Lookups Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, and wildcard searches, allowing you to seamlessly find specific values embedded in complex data cells. Experience a familiar, highly compatible interface to manage your data for free.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your mixed-value data.
- 2. Start the XLOOKUP function: Select an empty cell for your formula output and type =XLOOKUP( to initiate the function.
- 3. Add wildcards to your search value: Enter your search cell reference wrapped in wildcard asterisks using ampersands (e.g., "*"&A2&"*").
- 4. Define arrays and match mode: Select your lookup column, highlight your return column, and input 2 for the wildcard match mode.
- 5. Complete the lookup: Press Enter to instantly retrieve your embedded data directly in WPS Spreadsheet.

Frequently Asked Questions
Why does my standard VLOOKUP return an #N/A error when searching comma-separated cells?
VLOOKUP defaults to an exact match. When your target number is mixed with other text or numbers in the same cell, an exact match fails. You must use wildcard characters to perform a partial match.
Can I use SEARCH or FIND functions instead of wildcards for this lookup?
Yes, you can combine LOOKUP or INDEX with the SEARCH function. SEARCH locates the position of a substring within a text string, making it an excellent alternative for partial text matches when wildcards are not preferred.
Does the wildcard lookup method work for purely numerical values?
Wildcards technically operate on text strings. If your search value is a number, concatenating it with asterisks (e.g., "*"&123456&"*") forces the spreadsheet to treat it as a text string during the search, which allows the partial match to succeed.




