Use INDEX MATCH to Return the Correct Excel Table Value
Question details
The user needs to return a total cost from an Excel table by matching both description and quantity.

- 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.
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.
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.
In the data-entry table, select the cell where the total cost should appear.
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.
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.

Use a Helper Column for a Standard Lookup
Use this approach if you prefer a standard MATCH formula or need to avoid entering an array formula in older Excel versions.
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. 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. Create source keys: In Sheet2 cell D2, enter =A2&"|"&B2, then fill the formula down through the source data.
- 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. 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.

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.




