• 462K Members
• 8,015 Online
• 559K Conversations

Frequent Visitor

# 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

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.

# Re: Index Match with multiple criteria

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies