How to Automatically Populate Excel Prices for Repeated Book Titles
Question details
The user needs to automatically fill corresponding prices into a spreadsheet whenever a repeated textbook title is entered.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing repetitive data entry where textbook titles are entered multiple times, and the associated prices need to be pulled automatically from a master reference list.
- Observed behavior
- The user is looking for a formula-based solution to match textbook titles against a lookup list and automatically populate the correct prices, rather than typing them manually.
Ensure you have created a separate master reference table containing all unique book titles in one column and their corresponding prices in the adjacent column.
Use the XLOOKUP Function (Microsoft 365 and Excel 2021)
The modern XLOOKUP function is the most efficient way to fetch matching prices from a lookup list without worrying about column order.
XLOOKUP is available in Microsoft 365 and Office 2021. It replaces older lookup functions by allowing you to easily specify a fallback value (like "Unknown") directly within the formula if a book title isn't found.
Note the range of your book titles (e.g., D2:D50) and your prices (e.g., E2:E50) in your master reference list.
Select the first empty cell in your target price column. Type the formula: =XLOOKUP(A2, D$2:D$50, E$2:E$50, "Unknown") where A2 is the book title you just entered.
Press Enter to see the populated price. Click the cell, hover over the bottom-right corner to see the fill handle, and double-click or drag it down to fill the rest of the column.
Use the VLOOKUP Function with IFERROR (Older Excel Versions)
For older versions of Excel that do not support XLOOKUP, VLOOKUP combined with IFERROR is a reliable method to look up and populate prices.
Automatically Fill Prices Using WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced data functions, including both XLOOKUP and VLOOKUP. You can easily set up reference tables, manage large datasets, and automate your repetitive data entry for free.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the textbook data.
- 2. Prepare the reference table: Ensure you have a dedicated sheet or area with all unique book titles and their respective prices.
- 3. Insert the lookup formula: In the target price cell, type your preferred formula, such as =XLOOKUP(A2, title_column, price_column, "Unknown").
- 4. Fill the remaining cells: Press Enter to retrieve the price, then click and drag the fill handle down to instantly populate prices for all repeated textbook titles.
Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error?
The #N/A error means Excel cannot find an exact match for the book title in your lookup list. Check for accidental trailing spaces, spelling mistakes, or ensure the title actually exists in the master list.
Can I use XLOOKUP if my prices are in a column to the left of my book titles?
Yes. Unlike VLOOKUP, which requires the lookup value to be in the first column, XLOOKUP can search in any direction. You simply highlight the title column as the 'lookup_array' and the left-side price column as the 'return_array'.
How do I prevent my lookup formulas from breaking when I sort my data?
You must use absolute references for your lookup ranges. By adding dollar signs to your range (for example, $D$2:$E$100 instead of D2:E100), you lock the reference table in place so the formula does not shift when sorting or moving cells.
