How to Match Stock Quantities Between Excel Worksheets Using EAN
Question details
The user needs to retrieve stock quantities from a secondary worksheet and display them next to matching EAN codes in their primary worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging inventory data from two separate worksheets, where one contains article names and the other contains stock quantities, linked by a common EAN code.
- Observed behavior
- The user requires an accurate formula solution to look up EAN values in the first sheet, find the exact match in the second sheet, and output the corresponding stock quantity.
Before applying any formulas, ensure that the EAN columns in both worksheets are formatted exactly the same way (either both as 'Text' or both as 'Number') to prevent formula #N/A errors.
Use the XLOOKUP Function
XLOOKUP is the most efficient and robust modern function for matching data between sheets, offering built-in error handling for missing values.
XLOOKUP allows you to search for an EAN code in your stock sheet and return the exact quantity without worrying about column order. It also lets you specify a custom message if an EAN is missing.
In Sheet1, click on the first cell in the column where you want the stock quantity to appear (for example, cell C2).
Type the following formula: =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,"Not found"). This assumes your EAN in Sheet1 is in cell A2, the EAN list in Sheet2 is in Column A, and the stock quantity in Sheet2 is in Column B.
Press Enter to execute the formula. Then, click the small square at the bottom-right of the cell (the fill handle) and drag it down to apply the formula to all remaining EAN codes in your list.
Use INDEX and MATCH
If you are using an older version of Excel that does not support XLOOKUP, the combination of INDEX and MATCH is the most reliable alternative.
Manage Inventory Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful, built-in formula capabilities including XLOOKUP, INDEX, and MATCH. It makes merging inventory data, cross-referencing EAN codes, and managing complex workbooks fast and incredibly intuitive.
- 1. Open your inventory file: Launch WPS Spreadsheet and open the workbook containing your EAN lists and stock quantities.
- 2. Verify data formatting: Highlight your EAN columns and ensure they are formatted uniformly as 'Text' or 'Number' via the Home tab.
- 3. Insert your lookup function: Select the destination cell, navigate to the Formulas tab, and insert the XLOOKUP or INDEX function.
- 4. Apply and save: Drag the formula down to complete your sheet, then save your file as an .xlsx document to share seamlessly with others.
Frequently Asked Questions
Why is my lookup formula returning an error even though the EAN codes look identical?
This commonly occurs due to formatting mismatches. If one worksheet stores the EAN as a 'Number' and the other as 'Text' (often indicated by a small green triangle in the corner of the cell), the formula will not recognize them as a match. Standardize both columns to the same format to resolve the issue.
Can I use VLOOKUP to match stock quantities using EAN codes?
Yes, you can use VLOOKUP, provided the EAN code column is the very first column in your lookup range on the stock worksheet. However, XLOOKUP and INDEX/MATCH are more flexible because they can pull data to the left or right of the lookup column.
What happens if there are duplicate EAN codes in my stock worksheet?
Standard lookup functions like XLOOKUP, VLOOKUP, or INDEX/MATCH will stop at the first matching EAN code they find and return only that stock quantity. To get an accurate total inventory for duplicate EANs, you should use the SUMIFS function instead to add all matching stock quantities together.
