How to Lookup a Product Price Based on Product and Price Level in Excel
Question details
The user needs to retrieve a specific product's price from a pricing table based on both the selected product name and the specified price level.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching and extracting specific pricing data from a multi-column pricing table using two-way lookup formulas.
- Observed behavior
- The user wants to dynamically retrieve either a single price for a specific tier or an array of all price levels for a given product.
Ensure your pricing table is organized with products in rows and price levels (e.g., Price A, Price B) in columns. Verify that the lookup values exactly match the spelling in your data table to avoid formula errors.
Use INDEX and MATCH to Return a Specific Price Level
Combine INDEX and MATCH to perform a two-way lookup, finding the exact intersection of a product row and a price level column.
This method is highly robust and works in nearly all versions of Excel. It uses two MATCH functions inside an INDEX function to locate the exact cell where your target product and price level intersect.
Click on the cell where you want the calculated price result to appear.
Input the formula: =INDEX($B$2:$F$9,MATCH(A17,$A$2:$A$9,0),MATCH($F$15,$B$1:$F$1,0)). Adjust the ranges ($B$2:$F$9 for the data, $A$2:$A$9 for products, $B$1:$F$1 for price headers) to match your specific worksheet layout.
Press Enter. The formula will pinpoint the row for the selected product and the column for the chosen price level, extracting the value at that intersection.
Use the FILTER Function to Return All Price Levels
Extract the entire row of prices for a specific product using the dynamic array FILTER function.
Use WPS Spreadsheet for Advanced Formula Lookups
WPS Spreadsheet fully supports advanced lookup and dynamic array formulas like INDEX, MATCH, and FILTER, allowing you to build complex pricing and inventory tools efficiently.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your pricing table workbook.
- 2. Enter your lookup formula: Select the target cell and type your INDEX/MATCH or FILTER formula just like you would in Microsoft Excel.
- 3. Calculate and format: Press Enter to compute the results, and use the quick formatting tools in the ribbon to apply currency styles to your prices.
Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
This typically happens when the lookup value (product name or price level) does not exactly match the text in the data table. Check for hidden trailing spaces, typos, or mismatched data types in both your search criteria and the source table.
Can I use VLOOKUP instead of INDEX and MATCH for this task?
A standard VLOOKUP can only search by row and requires a static column index number. To search dynamically across both rows (products) and columns (price levels), you need a two-way lookup. You can achieve this by nesting a MATCH function inside a VLOOKUP, but INDEX and MATCH is generally preferred for its flexibility and performance.
Is the FILTER function available in all versions of spreadsheet software?
No, the FILTER function is a modern dynamic array formula available only in newer versions of Microsoft Office and WPS Office. If you are using an older spreadsheet version, you should use the INDEX and MATCH solution instead, as it is backward compatible with legacy software.
