Function Problems

Use XLOOKUP to Match Company Names by Store Number in Excel

Steven ParkerSteven Parker Nov 11, 2026 869 views

Question details

The user needs to match company data based on unique store numbers embedded within inconsistent text strings.

How to Use XLOOKUP to Match Company Names by Store Number
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a Helper Column

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.

2
Apply the Digit Extraction Formula

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.

3
Perform the XLOOKUP

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.

Use TEXTJOIN and SEQUENCE to Extract Numbers for XLOOKUP
Version Requirement: This formula relies on the SEQUENCE function, which is only available in Microsoft 365 and Excel 2021 or later.
Advanced Functions in WPS Spreadsheet

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. 1. Open Your Dataset: Launch WPS Office, open the Spreadsheet module, and select your .xlsx file containing the messy company names.
  2. 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. 3. Execute XLOOKUP: Use the built-in XLOOKUP feature from the Formulas tab to match your cleaned data seamlessly across different tables.
  4. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight software that processes complex array formulas smoothly.Supports advanced functions required for flexible data matching without a steep learning curve.Free to download and use on Windows, Mac, Linux, and mobile devices.
QA img-9

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.