Fix an Excel INDEX MATCH Formula with Multiple Criteria
Question details
Return a value from column Q by matching three criteria against columns B, A, and K.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet stores lookup criteria in N6, O6, and P6 and the corresponding data in columns B, A, K, and Q.
- Observed behavior
- The user needs a correctly parenthesized formula with aligned array references that returns the matching value from column Q.
Check that N6, O6, and P6 contain the values to match against columns B, A, and K, respectively. Make sure all lookup and return ranges cover the same rows, and adjust row 100 to include your data.
Use INDEX MATCH with Three Criteria
Use this formula to find the first row that meets all three criteria and return its value from column Q.
Each comparison creates an array of true or false results. Multiplying the arrays identifies rows where all three criteria match, and MATCH uses 0 for an exact match.
Click the cell where you want the value from column Q to appear.
In the formula bar, enter =IFERROR(INDEX($Q$2:$Q$100,MATCH(1,($B$2:$B$100=N6)*($A$2:$A$100=O6)*($K$2:$K$100=P6),0)),"").
Press Enter in Microsoft 365. In older Excel versions, confirm the array formula with Ctrl+Shift+Enter on Windows.
Compare the returned value with the first row where B matches N6, A matches O6, and K matches P6. If needed, edit the ranges in the formula bar so they include all data rows.

Use XLOOKUP for Multiple Criteria in Microsoft 365
Use XLOOKUP for a shorter formula when it is available in your Excel version.
Find a Match Across Multiple Criteria in WPS Spreadsheets
Use INDEX and MATCH in WPS Spreadsheets to find the first row matching three criteria and return the corresponding value from column Q.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Check the criteria: Verify that N6, O6, and P6 contain the values to match against columns B, A, and K.
- 3. Enter the formula: Select the result cell and enter =IFERROR(INDEX($Q$2:$Q$100,MATCH(1,($B$2:$B$100=N6)*($A$2:$A$100=O6)*($K$2:$K$100=P6),0)),"") in the formula bar.
- 4. Confirm and check: Press Enter and check the returned value. If your WPS version requires array entry, confirm the formula with Ctrl+Shift+Enter on Windows.

Frequently Asked Questions
Why does MATCH use 0 as its final argument?
The 0 tells MATCH to find an exact match for 1 in the combined criteria array. Without it, MATCH may use an approximate match and return an incorrect row.
What happens if more than one row matches all three criteria?
Both formulas return the value from the first matching row within the specified ranges. Check the source rows if you expect a different result.
Why is my INDEX MATCH result blank?
The IFERROR wrapper returns a blank for any error, including when no row matches. Check the criteria and range endpoints, or temporarily remove IFERROR in the formula bar to see the underlying error.
Can I use entire columns in the formula?
You can reference entire columns, but array calculations over full columns may be slow. Use bounded ranges such as $B$2:$B$100 and ensure every lookup and return range covers the same rows.
Do I need Ctrl+Shift+Enter for this formula?
In Microsoft 365, press Enter. In older Excel versions on Windows, confirm the INDEX MATCH array formula with Ctrl+Shift+Enter.




