Fix Excel INDEX MATCH with Two Row Criteria and One Header Criterion
Question details
The user needs to retrieve a value from an Excel wage matrix by matching two row criteria and one column-header criterion.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A wage matrix contains two identifying fields in separate columns and pay values under multiple header columns.
- Observed behavior
- The multi-criteria INDEX MATCH formula may return an error or fail to calculate correctly when array entry, range dimensions, or criteria values are not handled correctly.
Confirm that the wage data range starts below the header row and that both row-criteria ranges contain the same number of rows. Check the lookup values for extra spaces and confirm that the requested header exactly matches a header in the matrix.
Use a Boolean multi-criteria INDEX MATCH formula
Use this method when you need the most reliable match for two row conditions and one wage-column header.
This formula finds the row where both criteria are true, then finds the requested column by matching its header. It avoids potential false matches that can occur when criteria are simply concatenated.
Assume the first row criterion is in B2:B100, the second is in C2:C100, wage headers are in D1:M1, and wage values are in D2:M100. Place the two row lookup values in H2 and H3, and the header lookup value in H4.
Select the result cell and enter: =INDEX($D$2:$M$100,MATCH(1,($B$2:$B$100=$H$2)*($C$2:$C$100=$H$3),0),MATCH($H$4,$D$1:$M$1,0))
In Microsoft 365, Excel for the web, and Excel 2021 or later, press Enter. In older desktop Excel versions, confirm the formula with Ctrl+Shift+Enter instead of Enter.
Change the values in H2, H3, or H4 to verify that the formula returns the wage at the matching row and header intersection.
Use concatenated criteria in a legacy array formula
Use this alternative when your existing formula already combines the two row criteria into a single lookup value.
Use WPS Spreadsheet to Match Wage Data by Multiple Criteria
WPS Spreadsheet lets you build a multi-criteria INDEX MATCH formula to locate a wage using two row values and a matching column header. Its familiar spreadsheet layout makes it easy to migrate an existing Excel wage matrix and adjust the formula ranges.
- 1. Open the wage matrix in WPS Spreadsheet: Open the .xlsx or .xls file in WPS Office and select the worksheet containing the wage table.
- 2. Identify data, criteria, and header ranges: Confirm the two identifying columns, the wage-value area, and the header row. Put the row criteria and requested wage header in separate lookup cells.
- 3. Insert the INDEX MATCH formula: Select the result cell and enter an INDEX formula with one MATCH for the multiplied row conditions and a second MATCH for the header row.
- 4. Test the lookup values: Change the criteria cells and verify that the returned value updates to the correct wage and header intersection.
Frequently Asked Questions
Why does my INDEX MATCH formula need Ctrl+Shift+Enter?
In legacy Excel versions, formulas that evaluate array expressions such as ($B$2:$B$100=$H$2)*($C$2:$C$100=$H$3) must be confirmed with Ctrl+Shift+Enter. Microsoft 365 and newer versions usually calculate them after you press Enter.
How do I match two columns and one header in Excel?
Use MATCH(1,(criteria_range1=value1)*(criteria_range2=value2),0) to find the row, then use MATCH(header_value,header_range,0) to find the column inside INDEX.
What causes #N/A in a multi-criteria INDEX MATCH formula?
#N/A usually means one of the row criteria or the column header has no exact match. Check spelling, extra spaces, text-versus-number formatting, and whether all referenced ranges cover the correct rows and columns.
Do all ranges need to be the same size in an INDEX MATCH formula?
The two row-criteria ranges must have identical starting and ending rows. The INDEX array must have the same number of data rows, while the header range must span the same number of columns as the INDEX array.
What happens if two rows match both criteria?
MATCH returns the position of the first matching row. Add a unique third condition if the combination should identify only one wage record.
