Function Problems

How to Lookup a Number Within a Cell Containing Multiple Values in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 2, 2026 869 views

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.

How to Lookup a Number Within a Cell Containing Multiple Values in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the lookup result to be displayed.

2
Enter the INDEX and MATCH formula

Type the formula: =INDEX(ReturnRange, MATCH("*"&SearchValue&"*", LookupRange, 0)) into the formula bar.

3
Define your specific ranges

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.

4
Apply the formula

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 INDEX and MATCH with Wildcards
Wildcard Concatenation: Using the ampersand (&) correctly concatenates the asterisk wildcards to your cell reference, converting the number into a partial-match text string.
Powerful Spreadsheet Functions

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. 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your mixed-value data.
  2. 2. Start the XLOOKUP function: Select an empty cell for your formula output and type =XLOOKUP( to initiate the function.
  3. 3. Add wildcards to your search value: Enter your search cell reference wrapped in wildcard asterisks using ampersands (e.g., "*"&A2&"*").
  4. 4. Define arrays and match mode: Select your lookup column, highlight your return column, and input 2 for the wildcard match mode.
  5. 5. Complete the lookup: Press Enter to instantly retrieve your embedded data directly in WPS Spreadsheet.
100% compatible with Microsoft Excel formulas like XLOOKUP, VLOOKUP, and INDEX/MATCHLightweight application that processes large data sets and complex calculations smoothlyFree, intuitive user interface that requires no learning curve or retrainingBuilt-in syntax guides to help you construct complex lookup formulas accurately
microsoft office alternative - wps office

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.