How to Retrieve a Customer-Specific Price Using XMATCH and INDEX in Excel
Question details
The user needs to retrieve specific product prices based on two dimensions: the product reference (row) and the customer identifier (column).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a product catalog where different customers or client tiers have different pricing columns.
- Observed behavior
- The user requires an efficient formula that dynamically looks up the correct row and column intersection to return the correct price.
Ensure your product catalog data is organized in a clear grid format, with product references in a single column and customer pricing tiers in the top header row. Verify that there are no merged cells within your lookup ranges.
Use INDEX and XMATCH for a Two-Way Price Lookup
Use a combination of INDEX to define the price grid, and two XMATCH functions to locate the exact row (product) and column (customer).
While XLOOKUP is powerful, performing a two-dimensional lookup (matching both a row and a column) is often handled more efficiently by combining INDEX with XMATCH. The first XMATCH finds the product's row, and the second finds the customer's column.
Click on the cell where you want the customer-specific price to appear (for example, L4).
Start by typing =INDEX($C$4:$D$7, replacing the range with your actual price data grid (excluding headers and ID columns).
Insert XMATCH(I4,$A$4:$A$7), to locate the specific product reference (I4) within the product list column ($A$4:$A$7).
Insert XMATCH("price client "&K4,$C$3:$D$3) to match the customer name header. You can combine strings using the ampersand (&) to match your specific header format if needed.
Wrap the entire formula in IFERROR to return a blank cell if no match is found. The final formula should look like: =IFERROR(INDEX($C$4:$D$7,XMATCH(I4,$A$4:$A$7),XMATCH("price client "&K4,$C$3:$D$3)),""). Press Enter and drag the fill handle down to apply it to other rows.
Use Nested XLOOKUP for a Two-Way Price Lookup
Alternatively, you can nest two XLOOKUP functions to achieve the exact same two-dimensional lookup without using INDEX and MATCH.
Effortlessly Manage Pricing Matrices with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like XLOOKUP, XMATCH, and INDEX, allowing you to build responsive pricing calculators with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your pricing matrix or catalog spreadsheet.
- 2. Input the Lookup Formula: Select the target cell and enter the identical INDEX and XMATCH formula to retrieve customer-specific prices.
- 3. Fill and Format: Drag the fill handle to apply the formula across your dataset, and format the resulting numerical values as currency.
Frequently Asked Questions
Why does my XMATCH formula return an #N/A error?
This usually happens if the lookup value (product reference or customer name) does not exactly match the data in your lookup array. Check both your lookup value and your data ranges for hidden leading or trailing spaces.
Can I use MATCH instead of XMATCH for older spreadsheet versions?
Yes. XMATCH is a newer version that defaults to an exact match. If you are using an older version of Excel, you can use the traditional MATCH function by adding a 0 as the third argument to force an exact match, like MATCH(I4, $A$4:$A$7, 0).
How do I handle missing price data in the catalog?
Wrapping your main lookup formula in the IFERROR function, such as =IFERROR([formula], "Not Found"), ensures that missing data displays a clean, readable message or a blank cell instead of a confusing #N/A error code.
