'%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 Automatically Fill Excel Descriptions and Prices from Another Sheet
Question details
The user wants to automatically retrieve and populate item descriptions and prices on an input sheet based on part numbers or OEM part numbers located in a separate pricing sheet.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Entering a part number into a specific cell to auto-populate related product data (description and price) from a reference dataset.
- Observed behavior
- The user needs the correct formula combination to look up a value across two different columns (Part No or OEM Part Number) and return data from the corresponding description and price columns.
Ensure both your input sheet and the pricing reference sheet are in the same workbook, and verify the exact column letters where your Part Numbers, OEM Part Numbers, Descriptions, and Prices are stored to avoid formula referencing errors.
Use IFERROR with INDEX and MATCH to Look Up Multiple Criteria
This method allows you to search for either a standard Part Number or an OEM Part Number and return the corresponding description or price.
By combining IFERROR with two INDEX and MATCH functions, you instruct Excel to first search for the standard part number. If it isn't found, the formula will automatically fall back and search for the OEM part number instead.
Click the cell on your INPUT sheet where you want the item Description to appear (for example, assuming your input value is typed in cell B25, select C25).
Type the formula: =IFERROR(INDEX(Pricing!B:B,MATCH(B25,Pricing!A:A,0)),INDEX(Pricing!B:B,MATCH(B25,Pricing!C:C,0))) and press Enter. This assumes your description is in column B, Part No in column A, and OEM in column C of the 'Pricing' sheet.
To retrieve the Price, click the next adjacent cell (e.g., D25) and enter: =IFERROR(INDEX(Pricing!D:D,MATCH(B25,Pricing!A:A,0)),INDEX(Pricing!D:D,MATCH(B25,Pricing!C:C,0))) and press Enter.
Select the newly filled description and price cells, then click and drag the fill handle (the small square at the bottom-right corner) down the columns to copy the formulas to the remaining rows.
Use standard INDEX and MATCH for a Single Lookup Column
If your workbook only requires you to search by a single part number type, you can use a simpler formula combination without IFERROR.
Auto-Fill Complex Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formula combinations like INDEX, MATCH, and IFERROR to help you automate data entry across multiple sheets quickly and accurately.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your INPUT and Pricing sheets.
- 2. Select the output cell: Click the cell where you need the auto-filled description to appear.
- 3. Input the formula: Type your =IFERROR(INDEX(...)) formula. WPS Spreadsheet will automatically display function tooltips to guide you through the correct syntax.
- 4. Auto-fill the column: Press Enter, then double-click the fill handle on the cell's bottom-right corner to instantly apply the formula to all rows below.
Frequently Asked Questions
Why is my lookup formula returning an #N/A error?
An #N/A error means the INDEX and MATCH functions could not find the exact lookup value in your specified ranges. Ensure that the part number exactly matches the reference sheet (check for hidden spaces) and that you are referencing the correct column letters.
Can I use VLOOKUP instead of INDEX and MATCH?
Yes, you can use VLOOKUP, but INDEX and MATCH is generally more flexible. VLOOKUP requires the lookup value to be in the leftmost column of the reference table, whereas INDEX and MATCH can look up data regardless of column order, making it easier to search OEM part numbers stored in alternative columns.
How do I look up data from a sheet in a completely different workbook?
To reference another workbook, you must include the workbook name enclosed in square brackets before the sheet name within the formula, such as '[Pricing.xlsx]Pricing!A:A'. It is highly recommended to keep both files open simultaneously to ensure the formulas update correctly and without access errors.
