How to Do an Excel Two-Way Approximate Lookup for Width and Height
Question details
The user needs to find a specific price from a grid based on width and height limits using approximate matching.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Retrieving pricing data where the input dimensions (width and height) fall into specific tiered ranges (e.g., width > 2.0 and <= 2.5) rather than exact matches.
- Observed behavior
- The user needs a formula setup that correctly identifies the next larger boundary in both the row and column to retrieve the corresponding intersection price.
Ensure your pricing table is organized with sorted boundary values in ascending order for both rows and columns. Make sure the lookup values use the exact same measurement units as the table headers.
Use Nested XLOOKUP Functions for Next-Larger Matching
The most efficient way to perform a two-way lookup for upper-bound tiered pricing.
XLOOKUP is a powerful function that allows for exact matches or the next larger item. By nesting one XLOOKUP inside another, you can simultaneously evaluate the width columns and height rows to locate the exact intersection price.
Ensure your row headers (e.g., Heights in B2:B4) and column headers (e.g., Widths in C1:L1) contain the upper limits of your pricing tiers, sorted in ascending order.
Click the empty cell where you want the final calculated price to appear.
Type the nested formula: =XLOOKUP(D8, B2:B4, XLOOKUP(D7, C1:L1, C2:L4, 0, 1), 0, 1). In this formula, D8 is your target height, D7 is your target width, and the 1 specifies the 'exact match or next larger item' match mode.
Press Enter. The inner XLOOKUP finds the correct column for the width, and the outer XLOOKUP uses that column array to find the correct row for the height.
Use Nested FILTER and TAKE Functions
An alternative approach utilizing dynamic arrays to filter out values below the thresholds and extract the first matching price.
Use WPS Spreadsheet for Advanced Data Lookups
WPS Office fully supports modern dynamic array functions including XLOOKUP and FILTER, allowing you to build complex two-way tiered pricing models seamlessly and efficiently.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your pricing grid.
- 2. Apply XLOOKUP: Use the exact same nested XLOOKUP syntax to handle your height and width pricing lookup.
- 3. Process Data Instantly: Drag the formula down to automatically calculate and retrieve pricing for thousands of dimension combinations instantly.
Frequently Asked Questions
Why is my nested XLOOKUP returning an #N/A error?
An #N/A error usually means your input dimensions exceed the maximum values in your pricing table headers. Ensure your table covers the maximum possible inputs, or use the 'if_not_found' argument in XLOOKUP (the '0' in the examples) to handle these cases.
Does XLOOKUP require the row and column headers to be sorted?
While XLOOKUP does not strictly require sorted arrays for exact matches, using an approximate match (match mode 1 or -1) works best when your headers are organized in ascending order. This ensures the formula logically grabs the 'next larger item' based on your grid's visual structure.
Can I use INDEX and MATCH instead of XLOOKUP for two-way approximate matching?
Yes, you can use an INDEX/MATCH/MATCH formula. However, the MATCH function with match type 1 finds the largest value that is less than or equal to the lookup value. This means your table bounds must represent lower limits instead of upper limits, requiring you to restructure your pricing grid.
