How to Match Old and New Item Numbers Using Excel Formulas
Question details
The user needs to automatically retrieve and update replacement item numbers based on original item numbers across different sheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a large inventory list where original item numbers need to be mapped to their replacement numbers located in a separate reference sheet.
- Observed behavior
- The user needs an automated formula to return the correct new number for each corresponding original number without doing it manually.
Ensure both your main inventory sheet and the reference mapping sheet are open in the same workbook, and take note of the exact column letters containing your old and new numbers.
Use the XLOOKUP Function (Recommended for Newer Versions)
XLOOKUP is the most efficient and straightforward way to match and retrieve new item numbers, available in modern spreadsheet software like Microsoft 365 and WPS Office.
XLOOKUP allows you to search for a value in one column and return a corresponding value from another, regardless of their order. It also has built-in error handling for unmatched items.
Click on the first empty cell in the 'New Number' column of your main inventory sheet where the updated number should appear.
Type the formula =XLOOKUP(B2, 'Inventory list'!$A:$A, 'Inventory list'!$B:$B, ""). Replace 'B2' with the cell containing your first old item number.
Ensure 'Inventory list'!$A:$A points to the exact column containing the old numbers in your reference sheet, and $B:$B points to the column with the new numbers.
Press Enter, then double-click the fill handle at the bottom-right corner of the cell to copy the formula down your entire inventory list.
Use INDEX and MATCH with IFERROR (For Older Excel Versions)
If you are using an older version of Excel that doesn't support XLOOKUP, the combination of INDEX and MATCH is a robust and flexible alternative.
Easily Match and Map Data with WPS Spreadsheet
WPS Office provides full support for advanced lookup functions like XLOOKUP and INDEX/MATCH, making it incredibly easy to manage large inventory lists and map old data to new records seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your inventory document containing both the old and new item numbers.
- 2. Input the lookup formula: Navigate to the formula bar and input your preferred XLOOKUP or INDEX/MATCH formula just as you would in Excel.
- 3. Drag and apply: Use the smart fill handle to instantly apply the matching formula to thousands of inventory rows in seconds.
Frequently Asked Questions
Why is my XLOOKUP formula returning an error or #N/A?
This usually happens if the old item number doesn't exist in the reference sheet. By adding "" at the end of the XLOOKUP formula (e.g., =XLOOKUP(A2, C:C, D:D, "")), you instruct the formula to return a blank cell instead of an error when a match isn't found.
Can I match item numbers if they are on completely different Excel files?
Yes, you can reference external workbooks in your formula. Both workbooks must be open when you create the formula. The formula will include the external file's name in brackets, such as =XLOOKUP(B2, '[Inventory.xlsx]Sheet1'!$A:$A, '[Inventory.xlsx]Sheet1'!$B:$B, "").
What is the difference between XLOOKUP and VLOOKUP for matching inventory?
XLOOKUP is much more flexible than VLOOKUP because it can search in any direction (left or right) and doesn't require you to count column index numbers. It also handles missing data gracefully without needing a separate IFERROR wrapper.
Why are my matched item numbers showing as text instead of numbers?
If the old and new item numbers have different data formats (e.g., one is formatted as text and the other as a number), the lookup might fail or return the wrong format. Ensure both columns are formatted identically by selecting them, right-clicking, choosing 'Format Cells', and setting them to 'General' or 'Number'.
