'%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 Use Excel VLOOKUP to Autofill SKU Numbers from Descriptions
Question details
The user needs to retrieve SKU numbers from a secondary lookup sheet and automatically fill them into the main sheet based on matching product descriptions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Matching product descriptions across multiple worksheets to seamlessly retrieve their corresponding SKU numbers for inventory management or reporting.
- Observed behavior
- The user aims to implement a formula that correctly references a description on Sheet1, finds it on Sheet2, and returns the appropriate SKU into the target column.
Verify that your lookup data on Sheet2 is organized properly, ensuring the product description column is located to the left of the SKU column when using traditional VLOOKUP.
Use the VLOOKUP Function to Retrieve SKUs
This is the standard and widely compatible method for finding matching data across sheets using Excel's VLOOKUP function.
VLOOKUP searches for a value in the leftmost column of a table and returns a value in the same row from a specified column. It requires the lookup value and the return value to be in a specific left-to-right order.
Click on cell D2 on Sheet1, which is where you want the corresponding SKU to appear.
Type the formula =VLOOKUP(H2,Sheet2!A:B,2,FALSE) and press Enter. This instructs the spreadsheet to look for the description from H2 within columns A and B on Sheet2, returning the exact match from the second column.
Hover over the bottom-right corner of cell D2 until the cursor turns into a plus icon, then click and drag down column D to apply the calculation to the rest of the rows.
Use the XLOOKUP Function (Newer Excel Versions)
A modern, highly flexible alternative to VLOOKUP that does not restrict the order of your lookup and return columns.
Use VLOOKUP and Autofill seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced data lookup functions like VLOOKUP and XLOOKUP, allowing you to easily match descriptions and autofill SKUs across worksheets with zero hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your primary and lookup data.
- 2. Insert the VLOOKUP formula: Click on cell D2, navigate to the Formula tab, or directly type =VLOOKUP(H2,Sheet2!A:B,2,FALSE).
- 3. Instantly autofill the column: Double-click the fill handle at the bottom right corner of D2 to instantly populate the SKU numbers for hundreds of rows.
Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error?
The #N/A error means the exact product description from Sheet1 cannot be found in the lookup column of Sheet2. Check for hidden trailing spaces, typos, or ensure that you are referencing the correct lookup column.
Does the lookup column need to be the first column for VLOOKUP?
Yes. When using VLOOKUP, the value you are searching for must be located in the leftmost column of your defined table array. If it is located to the right of your return values, you should use XLOOKUP or an INDEX and MATCH combination instead.
How do I lock the table array range in my formula?
If you are selecting a specific range instead of whole columns (e.g., A2:B100), you must lock the range by selecting it in the formula bar and pressing F4. This changes the reference to $A$2:$B$100, preventing the range from shifting when you autofill down.
