Formula Errors

Use INDEX MATCH to Return the Correct Excel Table Value

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

The user needs to return a total cost from an Excel table by matching both description and quantity.

Use INDEX MATCH to Return the Correct Excel Table Value
Product
Microsoft Excel
Device & OS
not provided
Scenario
A data-entry table needs to retrieve the total cost from a source table for each matching description and quantity.
Observed behavior
The nested MATCH formula does not identify the correct source row.
Before you start

Confirm that the source table is named Table2 and contains Qty, Description, and Total Cost columns. Check that quantities are stored consistently and descriptions match between the source and input tables.

Solution 1Recommended

Match Quantity and Description in One Formula

Use a two-condition INDEX MATCH formula to return the total cost from the first row that matches both values.

1
Select the result cell

In the data-entry table, select the cell where the total cost should appear.

2
Enter the lookup formula

Enter =IFERROR(INDEX(Table2[Total Cost],MATCH(1,(Table2[Qty]=[@Qty])*(Table2[Description]=[@Description]),0)),""). Adjust the table and column names if yours are different.

3
Confirm and check the result

Press Enter. In older Excel versions, press Ctrl+Shift+Enter instead to confirm the array formula. Fill the formula down if Excel does not automatically apply it to the table column.

Match Quantity and Description in One Formula
How matches are handled: The formula returns the first row where both conditions match. IFERROR displays a blank if no match is found or another error occurs.
WPS Spreadsheets

Look Up Total Costs by Two Criteria in WPS Spreadsheets

Use a helper column with INDEX and MATCH in WPS Spreadsheets to find a total cost by quantity and description.

  1. 1. Open the workbook: Open the workbook in WPS Spreadsheets. For this example, place source quantities, descriptions, and costs in Sheet2 columns A, B, and C, starting in row 2.
  2. 2. Create source keys: In Sheet2 cell D2, enter =A2&"|"&B2, then fill the formula down through the source data.
  3. 3. Enter the lookup: On Sheet1, place the requested quantity in A2 and description in B2. In C2, enter =IFERROR(INDEX(Sheet2!$C$2:$C$1000,MATCH(A2&"|"&B2,Sheet2!$D$2:$D$1000,0)),""). Adjust the range if the source data extends beyond row 1000.
  4. 4. Check and fill the formula: Press Enter, compare the result with the matching source row, and fill the formula down column C for additional requests.
Match quantity and description with a single lookup keyOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and worksheet controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula multiply the two conditions?

Each comparison produces true or false values. Multiplying them produces 1 only where both quantity and description match, so MATCH(1,...,0) finds the first qualifying row.

Why is the result blank even though I expect a match?

IFERROR hides errors by returning a blank. Check for mismatched quantity types, extra spaces in descriptions, and incorrect table or column names. Temporarily remove IFERROR to see the underlying error.

What happens if several rows have the same quantity and description?

MATCH returns the position of the first matching row, so INDEX returns that row's total cost. Add another criterion if the combination is not unique.

Do I always need to press Ctrl+Shift+Enter?

Modern Excel versions generally accept the two-condition formula with Enter. Older versions may require Ctrl+Shift+Enter; the helper-column approach uses a standard lookup instead.