Index Match with multiple criteria

Highlighted
Frequent Visitor

Greetings,

 

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
Highlighted

@WakandaTech1993 

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.