Use INDEX(MATCH) to find value based on multiple criteria

Question

I am using INDEX(MATCH) to look up a value in one column using criteria from two different cells in another sheet. Here is the formula I am trying to use:

=INDEX(Sheet1!P:P,MATCH(1,(Sheet1!A:A=Sheet2!C$1)*(Sheet1!B:B=Sheet2!$B2),0))

Sheet1 is the array and column P contains the values I am wanting the formula to return. Column A in Sheet1 contains the values for the first criteria and Column B in Sheet1 contains the values for the second criteria. The criteria are represented in C1 and B2 of Sheet2. This will change as the cell is copied. Can anyone see any errors in this formula? It is returning a "value is not available for the formula or function."

Are you entering it as an array formula by hitting Ctrl-Shift-Enter when leaving edit mode? — Scott Craner

Unknown Unknown · Accepted Answer · 2016-07-15T04:18:08

This will have to be entered as an array formula¹ so the full column references should be cut down to a minimum size or you will experience unnecessary calculation lag as hundreds of thousands of blank cells are processed.

With text in column A,

=index(Sheet1!$P$1:index(Sheet1!$P:$P, match("zzz", Sheet1!$A:$A)),
   match(1, (Sheet1!$A$1:index(Sheet1!$A:$A, match("zzz", Sheet1!$A:$A))=Sheet2!C$1)*
            (Sheet1!$B$1:index(Sheet1!$B:$B, match("zzz", Sheet1!$A:$A))=Sheet2!$B2), 0))

With numbers or dates in column A,

=index(Sheet1!$P$1:index(Sheet1!$P:$P, match(1e99, Sheet1!$A:$A)),
   match(1, (Sheet1!$A$1:index(Sheet1!$A:$A, match(1e99, Sheet1!$A:$A))=Sheet2!C$1)*
            (Sheet1!$B$1:index(Sheet1!$B:$B, match(1e99, Sheet1!$A:$A))=Sheet2!$B2), 0))

You did not have absolute reference anchors (e.g. $ ) in the original Sheet1 range references but the way you had them set up for the criteria from Sheet2 led me to believe that you required them for both row and column.

If you have column header labels in row 1, change A1, B1 and P1 to A2, B2 and P2.

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

Use INDEX(MATCH) to find value based on multiple criteria

1 Answers