Return a Price Based on Width and Drop in Excel
Question details
Return a price automatically by matching a width and a drop to a two-dimensional price table.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters width and drop measurements and needs the price at their intersection in a table.
- Observed behavior
- The desired result is the price where the matching drop row and width column intersect.
Arrange drop measurements in one column, width measurements in one row, and prices in the cells where they intersect. Check that the entered measurements use the same units and data types as the table headers.
Use INDEX and MATCH to find the price
Use this two-way lookup when the entered width and drop must match table headers exactly.
The first MATCH finds the drop row, and the second MATCH finds the width column. INDEX returns the price at their intersection.
In Excel, place drop values in A2:A4, width values in B1:D1, and the corresponding prices in B2:D4.
Type the required drop in H2 and the required width in H3.
Select an empty result cell and enter =INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0)). Press Enter to return the matching price.
In the formula bar, replace the example ranges with your actual price, drop, and width ranges. Keep the drop range aligned with the price rows and the width range aligned with the price columns.

Look up width and drop prices in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use the same INDEX and MATCH formula to return the price at the intersection of two measurements.
- 1. Open the workbook: In WPS Spreadsheets, choose File > Open and select your Excel price workbook.
- 2. Enter the measurements: Enter the drop in H2 and the width in H3, or identify the cells you want to use for those inputs.
- 3. Add the formula: Select an empty cell and enter =INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0)). Replace the example references if your table is arranged differently.
- 4. Check and save: Press Enter, verify the returned price against the table, and choose File > Save.

Frequently Asked Questions
Why does the formula return #N/A?
One or both measurements may not exactly match the table headers. Check H2 and H3 against the drop and width ranges, including whether the values are stored as numbers or text.
Can I show a message instead of #N/A?
Wrap the formula in IFERROR, for example: =IFERROR(INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0)),"Price not found"). This also hides other formula errors, so check the ranges if the message appears unexpectedly.
What if my price table is larger than the example?
Edit the formula in the formula bar to include the full price block and its corresponding drop and width headers. The drop range must cover the same rows as the price block, and the width range must cover the same columns.
Will the formula find the nearest width or drop?
No. The 0 in each MATCH requires an exact match. A nearest-value lookup needs different matching rules and a decision about how dimensions should be rounded.




