Function Problems

Return a Price Based on Width and Drop in Excel

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Return a price automatically by matching a width and a drop to a two-dimensional price table.

Return a Price Based on Width and Drop in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the example table

In Excel, place drop values in A2:A4, width values in B1:D1, and the corresponding prices in B2:D4.

2
Enter the measurements

Type the required drop in H2 and the required width in H3.

3
Enter the lookup formula

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.

4
Adjust the ranges

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.

Use INDEX and MATCH to find the price
Exact matches required: The 0 in each MATCH requires an exact match. If either measurement is missing or stored differently from its table header, the formula may return #N/A.
Work with price tables in WPS

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. 1. Open the workbook: In WPS Spreadsheets, choose File > Open and select your Excel price workbook.
  2. 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. 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. 4. Check and save: Press Enter, verify the returned price against the table, and choose File > Save.
Find prices using exact width and drop matchesOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and controlsWork with a free, lightweight office suite
microsoft office alternative - wps office

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.