'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Link Excel SKU Numbers to Product Names with Lookup Formulas
Question details
The user needs to connect SKU numbers to product names automatically in Excel for tracking inventory, shipments, and reporting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing inventory or sales data where entering an SKU should automatically populate the corresponding product name.
- Observed behavior
- The user wants to implement a lookup formula to automatically display the correct product name based on the entered SKU instead of typing it manually.
Ensure you have a dedicated reference sheet or table that contains all unique SKU numbers in one column and their corresponding product names in the adjacent column.
Link SKU to Product Name Using the VLOOKUP Formula
VLOOKUP is the standard method for searching a reference table and returning matching data from a specific column.
The VLOOKUP function looks for a specific value in the first column of an array and returns a value in the same row from a column you specify. This is ideal for pulling product names into a sales or shipment sheet based on an SKU.
On a new or existing sheet (e.g., named 'ReferenceTable'), list your SKUs in Column A and Product Names in Column B.
Go to your main data sheet (e.g., 'Consumed' or 'Inventory') and click on the cell where you want the product name to appear (e.g., cell B2).
Type =VLOOKUP(C2,ReferenceTable!A:B,2,FALSE) where C2 is the cell containing the SKU you want to look up.
Press Enter to see the product name, then drag the fill handle at the bottom right corner of the cell down to copy the formula to other rows.
Link SKU to Product Name Using the XLOOKUP Formula
XLOOKUP is a modern and flexible alternative to VLOOKUP, allowing you to search any column and set a default 'Not found' message.
Easily Manage Inventory and Lookups with WPS Spreadsheet
WPS Office provides a highly compatible and free Spreadsheet tool that fully supports advanced formulas like VLOOKUP, XLOOKUP, and INDEX/MATCH to help you link SKUs to product names effortlessly.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your inventory or sales workbook containing the SKU data.
- 2. Use the Insert Function feature: Click the 'Formulas' tab, then select 'Insert Function' to easily search for VLOOKUP or XLOOKUP.
- 3. Fill in the function arguments: Use the prompt dialog box to click your SKU cell, highlight your reference table, and specify the column index.
- 4. Complete and drag to fill: Click OK to apply the formula, then double-click the bottom-right corner of the cell to populate the rest of your column automatically.
Frequently Asked Questions
Why does my VLOOKUP formula return an #N/A error?
An #N/A error occurs if the SKU entered does not exist in the reference table, or if there are trailing spaces in the text. Ensure both the lookup value and the reference table data match exactly, and check for hidden spaces.
Can I link product names across different workbook files?
Yes, you can reference a table in a completely different workbook. Keep both files open, type `=VLOOKUP(`, and then switch to the other workbook to select the reference table. Excel will automatically generate the correct file path in the formula.
What is the advantage of using XLOOKUP instead of VLOOKUP?
XLOOKUP can look up data in any direction (left or right), whereas VLOOKUP only searches the leftmost column of the highlighted array. Additionally, XLOOKUP has a built-in argument to handle missing data without needing to wrap the formula in a separate IFERROR function.
