Forum Discussion

WakandaTech1993's avatar
WakandaTech1993
Copper Contributor
Jun 11, 2019

Index Match with multiple criteria

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. 

excel

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 13, 2019

    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.

Resources