How to Use Excel Lookup Formulas to Match Part Numbers Between Sheets
Question details
The user needs to return quantities from one Excel worksheet to another by matching part numbers that are in a different order, avoiding spill or lookup errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Matching inventory or part numbers across multiple worksheets to retrieve associated quantities.
- Observed behavior
- Attempts to use formulas resulted in spill errors or other incorrect outputs due to mismatched order and potentially incorrect formula application.
Ensure that your part numbers are formatted identically (e.g., both as text or both as numbers) in both worksheets to prevent matching errors, and remove any hidden trailing spaces.
Use the XLOOKUP Function
The most robust and straightforward method for modern Excel versions to look up data across sheets without worrying about column order.
XLOOKUP is the recommended modern replacement for older lookup functions. It allows you to search for a part number in a source column and return the corresponding quantity from another column, regardless of their arrangement.
In your destination worksheet, click the cell where you want the matched quantity to appear (for example, B2).
Type the formula =XLOOKUP(A2, Tab1!$A:$A, Tab1!$B:$B, "Not found") where A2 is the part number to search for, Tab1!$A:$A is the column containing part numbers in the source sheet, and Tab1!$B:$B is the column with the quantities.
Press Enter to get the result. Then, double-click or drag the fill handle (the small square at the bottom-right corner of the cell) down to copy the formula for the remaining part numbers.
Use the VLOOKUP Function for Older Versions
A widely used alternative if you are using an older version of Excel that does not support the XLOOKUP function.
Use INDEX and MATCH for Complex Lookups
Ideal for older Excel versions where the return column is located to the left of the lookup column.
Seamlessly Match Data Across Sheets with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced lookup formulas like XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to easily cross-reference part numbers and extract data without errors.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your part numbers.
- 2. Use the Formula Builder: Click on the 'Formulas' tab and select 'Insert Function' to easily find and insert XLOOKUP or VLOOKUP.
- 3. Select your arrays: Follow the on-screen prompts to select your lookup value, lookup array, and return array across your different sheets.
- 4. Drag to fill: Apply the formula to your entire dataset instantly using the smart fill handle.
Frequently Asked Questions
Why am I getting a #SPILL! error when matching part numbers?
A #SPILL! error occurs when a dynamic array formula does not have enough empty cells to display the results. Ensure the cells directly below or next to your formula are completely blank and contain no hidden spaces.
How do I fix a #N/A error in my lookup formula?
The #N/A error means the exact part number was not found. Check for trailing spaces, mismatched data types (e.g., one sheet using text format and the other using number format), or use the 'if_not_found' argument in XLOOKUP to return a custom message like 'Not found'.
Can I match part numbers if the source columns are not next to each other?
Yes. If using XLOOKUP or INDEX/MATCH, the lookup column and return column can be anywhere in your workbook. VLOOKUP, however, requires the lookup column to be to the left of the return column.
How do I handle duplicate part numbers in my source sheet?
Standard lookup functions return the first match they find. If you have duplicates, consider using the FILTER function or a PivotTable to aggregate quantities, or sort your data to ensure the most relevant match appears first.
