How to Compare Normalized IDs with XLOOKUP in Excel
Question details
The user needs to compare IDs across two data sets where formatting differs, such as one containing dashes or leading zeros, to retrieve corresponding values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Matching and retrieving data from column C into column D based on an ID in column A that corresponds to an inconsistently formatted ID in column B.
- Observed behavior
- To successfully find a matching ID in the entire column B, regardless of differing dashes or leading zeros, and accurately return the related value from column C.
Ensure your spreadsheet software supports the XLOOKUP function, which is available in Microsoft Office 365, Excel 2021, and modern spreadsheet tools like WPS Office.
Use XLOOKUP with the SUBSTITUTE Function to Remove Dashes
This method dynamically removes formatting characters like dashes from both the lookup value and the lookup array to find a perfect match.
When comparing data sets with inconsistent punctuation, you must normalize both the search key and the target range. The SUBSTITUTE function seamlessly handles this within the XLOOKUP formula, allowing it to search the entire column rather than just comparing row by row.
Click on cell D1, where you want the matched value from column C to appear.
Type the formula =XLOOKUP(SUBSTITUTE(A1,"-",""),SUBSTITUTE(B:B,"-",""),C:C,"") into the formula bar.
Press Enter to get the result. Click the fill handle in the bottom-right corner of cell D1 and drag it down to apply the formula to the rest of column D.
Handle Leading Zeros by Standardizing Text Length
If your IDs contain leading zeros that cause mismatches, standardize them by formatting both columns as text or using the TEXT function.
Use WPS Spreadsheet to Compare Complex Data Sets
WPS Spreadsheet fully supports advanced functions like XLOOKUP and SUBSTITUTE, making data normalization and comparison quick and entirely compatible with your existing Excel files.
- 1. Open your data file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the mismatched IDs.
- 2. Select the destination cell: Click on cell D1 to prepare for the lookup.
- 3. Insert the XLOOKUP function: Go to the Formula tab, click 'Insert Function', or directly type =XLOOKUP(SUBSTITUTE(A1,"-",""),SUBSTITUTE(B:B,"-",""),C:C,"") into the formula bar.
- 4. Execute and copy: Press Enter to see the matched data, then double-click or drag the bottom right corner of the cell downwards to fill the remaining rows.
Frequently Asked Questions
Why does my XLOOKUP return an error when matching identical IDs?
This usually happens due to hidden characters, trailing spaces, or inconsistent formatting (like text vs. numbers). Using functions like TRIM or SUBSTITUTE inside XLOOKUP can normalize the data for an accurate match.
Can I use VLOOKUP instead of XLOOKUP for this task?
While VLOOKUP can be used, it does not support passing arrays directly into the lookup range without using an array formula (Ctrl+Shift+Enter) in older versions. XLOOKUP handles array operations natively and is more efficient for this type of comparison.
How do I remove multiple different characters from an ID?
You can nest multiple SUBSTITUTE functions together. For example, to remove both dashes and spaces, you can use SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","") as your lookup value in the formula.
