Index Match with multiple criteria

Frequent Visitor



I'm working in Excel 2013, and I'm using an Index Match Function with multiple criteria. The function should allow me pull in a Task based on:

1. Team Member

2. Date

3. Task Number


Any assistance is welcomed! I've attached the file for a reference


Formulas I've Tried

1.) =INDEX(A1:E19,MATCH(1,(A:A=I2)*(B:B=N2)*(D:D=H3),0),4) --> Returns #N/A

2.) ={INDEX(A1:E19,MATCH(1,(A:A=I2)*(B:B=N2)*(D:D=H3),0),4)} --> With Array Returns #N/A 

3.) =INDEX(D2:D19,MATCH(I2,A2:A19,0),MATCH(N2,B2:B19,0),MATCH(H3,C2:C19,0)) --> Returns #REF 

Note: Formula number 2 does return the desired task for the date of 6/12/2019, but does not work for 6/11/19 or 6/13/2019. 

1 Reply


It could be

=IFERROR(INDEX($D:$D,MATCH(1,INDEX(($A:$A=$H$2)*($B:$B=$M$2)*($C:$C=G$3),0),0)),"no such")

and I guess you shall use column C instead of column D as in your formulas, at least for the sample file. Plus, at least for the tasks, you have no match names: "Task1" and "Task 1"; "Task 3" and "Task 3 "

Result is in G6:I6 attached.