How to Match Part Numbers Between Excel Sheets and Return Descriptions
Question details
The user needs to match part numbers located in column B across two separate Excel worksheets and retrieve the corresponding descriptions from column A.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing inventory or parts lists across two different worksheets to pull corresponding descriptions based on matching part numbers.
- Observed behavior
- The goal is to successfully display the old description beside the new description for each matching part number without needing to rearrange columns to accommodate a standard VLOOKUP.
Ensure both of your worksheets are open within the same workbook, and take note of the exact row numbers containing your part numbers and descriptions to prevent formula reference errors.
Use INDEX and MATCH to Return Data from the Left
This is the most flexible and highly recommended method. When your lookup value (part number) is to the right of your return value (description), INDEX and MATCH can search backwards, bypassing the limitations of VLOOKUP.
The INDEX function retrieves a value from a specified array, while the MATCH function identifies the exact row position of your part number. Together, they allow you to search column B and return a value from column A.
Click the cell in your main worksheet where you want the returned description to appear.
Type the formula: =IFERROR(INDEX(Sheet2!$A$2:$A$100,MATCH(B2,Sheet2!$B$2:$B$100,0)),""). Be sure to adjust the sheet names and cell ranges to match your specific workbook.
Press Enter on your keyboard to execute the formula and retrieve the matching description.
Click and drag the small fill handle at the bottom right corner of the cell to copy the formula down for the remaining part numbers in your list.
Rearrange Columns and Use VLOOKUP
If you strongly prefer using the familiar VLOOKUP function, you must first modify your source data layout so that the lookup column is placed first.
Easily Handle Complex Formulas with WPS Office
WPS Spreadsheet provides robust support for advanced data lookup functions like INDEX, MATCH, XLOOKUP, and VLOOKUP. It is a highly compatible and lightweight tool for handling large datasets and complex formulas efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your part numbers and descriptions.
- 2. Access the function library: Select the cell where you want the result, navigate to the Formulas tab, and click 'Insert Function'.
- 3. Insert the lookup formula: Search for INDEX or MATCH in the dialog box, and use the intuitive interface to select your arrays and lookup values without typing syntax manually.
- 4. Process the data: Click OK to apply the function, then double-click the fill handle to instantly populate the rest of the column.
Frequently Asked Questions
Why is my formula returning an #N/A error?
An #N/A error means the formula cannot find a matching part number in the lookup range. Wrapping your formula in IFERROR, like =IFERROR(INDEX(...), ""), will return a clean, blank cell instead of an error message when no match is found.
Can I use XLOOKUP instead of INDEX and MATCH?
Yes. If your spreadsheet software supports XLOOKUP, it is an excellent alternative that can look in any direction. You would write it as: =XLOOKUP(B2, Sheet2!$B$2:$B$100, Sheet2!$A$2:$A$100, "").
Why did my cell references change when I copied the formula down?
If you do not lock your lookup ranges with absolute references (using the dollar sign, like $A$2:$A$100), the spreadsheet automatically shifts the reference rows down as you drag the formula. Select the range in your formula bar and press F4 to convert it to an absolute reference.
