Formula Errors

Fix an Excel INDEX MATCH Formula with Multiple Criteria

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Return a value from column Q by matching three criteria against columns B, A, and K.

Fix an Excel INDEX MATCH Formula with Multiple Criteria
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the cell where you want the value from column Q to appear.

2
Enter the formula

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)),"").

3
Confirm the formula

Press Enter in Microsoft 365. In older Excel versions, confirm the array formula with Ctrl+Shift+Enter on Windows.

4
Check the result

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 INDEX MATCH with Three Criteria
A blank result can hide errors: IFERROR returns an empty string for any formula error, including a missing match. Temporarily remove the IFERROR wrapper if you need to diagnose an error.
Work with spreadsheet formulas in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Check the criteria: Verify that N6, O6, and P6 contain the values to match against columns B, A, and K.
  3. 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. 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.
Match values across three columns in one formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface to review formulas and results
QA img-9

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.