How to Look Up Part Quantities in Excel using XLOOKUP or INDEX MATCH
Question details
The user needs to search for specific part numbers and retrieve their corresponding quantities from a different column.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing an inventory report or data sheet where part numbers listed in column A need their respective quantities automatically pulled from columns L and M.
- Observed behavior
- The user wants to automatically populate column B with the matching quantities and leave the cell completely blank if the part number is not found.
Verify that your data does not contain hidden trailing spaces or inconsistent text formatting, as these will cause exact-match lookup formulas to fail. Converting your data ranges into structured Tables can also help prevent range-shifting issues when copying formulas.
Use the XLOOKUP Function to Retrieve Quantities
The XLOOKUP function is the most modern and efficient way to look up values, as it natively handles missing matches without needing additional error-handling formulas.
XLOOKUP searches a specified array or range and returns an item corresponding to the first match it finds. Unlike older functions, it has built-in arguments to specify what should happen if a match isn't found.
Click on cell B2 (or the first cell in your destination column) where you want the retrieved quantity to be displayed.
Type the formula =XLOOKUP(A2,$L:$L,$M:$M,"") directly into the formula bar.
Press Enter. If the part number from A2 exists in column L, its corresponding quantity from column M will appear. If it does not exist, the cell will remain blank.
Click the small square at the bottom-right corner of cell B2 and drag it down the column, or double-click the fill handle to automatically copy the formula to all adjacent rows.
Use INDEX and MATCH with IFERROR for Older Versions
If you are using an older version of Excel or a spreadsheet program that does not support XLOOKUP, the combination of INDEX, MATCH, and IFERROR is the most robust alternative.
Easily Manage Inventory and Look Up Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, VLOOKUP, and INDEX MATCH. It handles massive datasets smoothly, allowing you to seamlessly look up part quantities, manage inventory, and analyze data without any lag.
- 1. Open your dataset: Launch WPS Spreadsheet and open your inventory or report file.
- 2. Insert the lookup formula: Select the target cell in Column B and type =XLOOKUP(A2,$L:$L,$M:$M,"") to instantly retrieve the quantity.
- 3. Apply to all rows: Double-click the fill handle on the cell to auto-fill the formula across thousands of rows in a single second.
- 4. Save and share: Save your work in standard .xlsx format to ensure seamless collaboration with Microsoft Excel users.
Frequently Asked Questions
Why is my lookup formula returning a #N/A error instead of a blank?
If you are getting a #N/A error despite using an error-handling formula, check for syntax issues. If you are not using XLOOKUP or IFERROR, the #N/A error is the default behavior when the exact part number cannot be found. This often happens due to hidden leading or trailing spaces. You can use the TRIM() function or the 'Text to Columns' tool to clean your data.
Can I use VLOOKUP instead of XLOOKUP for this task?
Yes, but VLOOKUP has stricter rules. It only works if the search column (Part Number) is positioned to the left of the return column (Quantity). Assuming Column L contains the part numbers and Column M contains the quantities, =IFERROR(VLOOKUP(A2,$L:$M,2,FALSE),"") will work perfectly. If the columns are reversed, VLOOKUP will fail, which is why XLOOKUP or INDEX MATCH is preferred.
How do I look up part quantities from another worksheet?
You can easily modify your formula to include the name of the other sheet. For example, if your part numbers and quantities are located on 'Sheet2', your XLOOKUP formula would look like this: =XLOOKUP(A2,Sheet2!$L:$L,Sheet2!$M:$M,""). Simply click on the target columns in the other sheet while writing the formula to let the software auto-fill the sheet names.
