Use XLOOKUP to Match Company Names by Store Number in Excel
Question details
The user needs to match company data based on unique store numbers embedded within inconsistent text strings.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing data lookups on company names formatted inconsistently by isolating and matching their unique store numbers.
- Observed behavior
- Standard lookups fail due to punctuation and spacing discrepancies, requiring digits to be extracted first to serve as a reliable lookup key.
Ensure that the store numbers embedded in your text strings are unique across your dataset. Verify that your version of Excel supports dynamic array formulas like SEQUENCE before proceeding.
Use TEXTJOIN and SEQUENCE to Extract Numbers for XLOOKUP
Extract only the numeric characters from a messy text string in Microsoft 365, then use the result as the lookup value in your XLOOKUP formula.
Because company names can have inconsistent spacing or symbols like '#', a standard XLOOKUP on the full name often fails. By isolating just the store number using Microsoft 365 dynamic array functions, you create a robust lookup key.
In your source data spreadsheet, click the column header next to the company names, right-click, and select Insert to create a new helper column to store the extracted store numbers.
Select the first cell in your helper column and input the formula `=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))` to pull only the digits from cell A2.
Click into the cell where you want your lookup outcome. Type `=XLOOKUP(extracted_value, helper_column_range, return_array)` and press Enter, substituting the extracted store number as your lookup value.

Remove Letters and Special Characters using TAKE and SCAN
Use a secondary advanced array formula approach to strip out non-numeric characters before performing the XLOOKUP.
Master Complex Data Matching Tasks with WPS Spreadsheet
WPS Spreadsheet offers powerful functions including modern lookup and text manipulation tools to handle messy data effortlessly. Avoid subscription fees and enjoy a familiar interface with robust formula support tailored for your daily data processing needs.
- 1. Open Your Dataset: Launch WPS Office, open the Spreadsheet module, and select your .xlsx file containing the messy company names.
- 2. Calculate Array Formulas: Highlight an empty cell, click the formula bar, and input text-extraction array formulas exactly as you would in standard Excel.
- 3. Execute XLOOKUP: Use the built-in XLOOKUP feature from the Formulas tab to match your cleaned data seamlessly across different tables.
- 4. Save Or Share: Click File > Save to retain your work in the universal .xlsx format, ensuring total compatibility when sharing with clients and colleagues.

Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP for this task?
Yes, but VLOOKUP requires the lookup column to be the very first column in your table array. XLOOKUP is more flexible because it can search in any direction using separate lookup and return arrays.
Why does the extraction formula use double hyphens (--)?
The double hyphen (--) is a double unary operator. It is used to convert boolean values (TRUE/FALSE) or text representations of numbers into actual numerical values so the ISNUMBER function can evaluate them correctly.
Will these dynamic array functions work in older versions of Excel?
No. Functions like SEQUENCE are only available in Microsoft 365, Excel 2021, and newer versions. Users on older versions will need to use complex INDEX/MATCH arrays or VBA macros instead.
Is there an easier way to extract numbers from text in the future?
Yes, new native REGEX functions are currently in testing for future Office updates. Once released broadly, they will allow for simpler number extraction from text strings without chaining together multiple intermediate functions.




