Jun 11 2019 11:37 AM
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.
Jun 13 2019 02:57 PM
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.