How to Automatically Retrieve a Price from Another Excel Sheet
Question details
The user needs to use a lookup formula to enter an experience name on one worksheet and automatically pull its matching price from a different worksheet.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up matching pricing records from a master data sheet and auto-filling them into a working sheet for 50 different items.
- Observed behavior
- The user wants to efficiently retrieve rates across worksheets and fill down the formula to identify all 50 gift experiences without manual entry.
Ensure both of your worksheets are within the same workbook and that the column containing your item names in the pricing sheet does not have exact duplicates, as this could cause the formula to pull the wrong price.
Use the VLOOKUP Function to Retrieve Prices
VLOOKUP is the standard and most widely compatible method to search for a value in one sheet and return a corresponding value from an adjacent column.
VLOOKUP looks for a specific value in the first column of your selected range and returns a value in the same row from a column you specify. To maintain accuracy, make sure to set the match type to FALSE for an exact match.
Navigate to Sheet 1 and click on cell F5, where you want the first matched price to appear.
Type the formula =VLOOKUP(B5,Sheet2!C:D,2,FALSE). In this formula, B5 is the item name you are looking up, Sheet2!C:D is the range containing your names and prices, 2 specifies that the price is in the second column of that range, and FALSE ensures an exact match.
Select cell F5 again. Hover your mouse over the small square in the bottom-right corner of the cell until the cursor turns into a solid black cross. Click and drag the cross down, or simply double-click it, to automatically apply the formula to the rest of your 50 items.
Use XLOOKUP for Flexible Retrieval
XLOOKUP is a modern, more robust alternative to VLOOKUP available in newer spreadsheet versions, allowing you to return custom text if an item is not found.
Easily Retrieve Data Across Sheets with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced lookup functions like VLOOKUP and XLOOKUP, allowing you to seamlessly link and retrieve pricing data across multiple worksheets. Its intuitive interface and smart-fill capabilities make managing bulk records effortless.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your item lists and pricing sheets.
- 2. Insert the Lookup Formula: Click on the cell where you want the price to appear (e.g., F5) and type =VLOOKUP(B5,Sheet2!C:D,2,FALSE).
- 3. Use Smart Fill to Apply to All Rows: Double-click the small square at the bottom-right of the selected cell to automatically fill the formula down to the end of your item list.
Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error?
An #N/A error typically means the exact lookup value wasn't found in your source sheet. Check for trailing spaces, spelling mistakes, or mismatched data types (such as numbers formatted as text) between the two sheets.
Can I lookup prices if they are located to the left of the item name?
VLOOKUP only searches from left to right, meaning the return column must be to the right of the lookup column. If your prices are on the left, you should use the XLOOKUP function or an INDEX and MATCH formula combination instead.
How do I update prices automatically if the rates change?
Formulas like VLOOKUP and XLOOKUP are dynamic. As long as the formula references the cells in your pricing sheet, any time you change a price on Sheet 2, the corresponding price on Sheet 1 will update automatically.
