Home

Index Match with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-684013%22%20slang%3D%22en-US%22%3EIndex%20Match%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684013%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20in%20Excel%202013%2C%20and%20I'm%20using%20an%20Index%20Match%20Function%20with%20multiple%20criteria.%20The%20function%20should%20allow%20me%20pull%20in%20a%20%3CU%3E%3CEM%3ETask%3C%2FEM%3E%3C%2FU%3E%20based%20on%3A%3C%2FP%3E%3CP%3E%3CEM%3E1.%20Team%20Member%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E2.%20Date%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E3.%20Task%26nbsp%3B%3C%2FEM%3E%3CI%3ENumber%3C%2FI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20is%20welcomed!%20I've%20attached%20the%20file%20for%20a%20reference%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EFormulas%20I've%20Tried%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E1.)%3C%2FSTRONG%3E%3C%2FEM%3E%20%3DINDEX(A1%3AE19%2CMATCH(1%2C(A%3AA%3DI2)*(B%3AB%3DN2)*(D%3AD%3DH3)%2C0)%2C4)%20--%26gt%3B%20Returns%20%23N%2FA%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E2.)%3C%2FSTRONG%3E%3C%2FEM%3E%20%3D%7BINDEX(A1%3AE19%2CMATCH(1%2C(A%3AA%3DI2)*(B%3AB%3DN2)*(D%3AD%3DH3)%2C0)%2C4)%7D%20--%26gt%3B%20With%20Array%20Returns%20%23N%2FA%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E3.)%3C%2FSTRONG%3E%3C%2FEM%3E%20%3DINDEX(D2%3AD19%2CMATCH(I2%2CA2%3AA19%2C0)%2CMATCH(N2%2CB2%3AB19%2C0)%2CMATCH(H3%2CC2%3AC19%2C0))%20--%26gt%3B%26nbsp%3BReturns%20%23REF%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENote%3A%20Formula%20number%202%20%3CU%3E%3CSTRONG%3Edoes%3C%2FSTRONG%3E%3C%2FU%3E%20return%20the%20desired%20task%20for%20the%20date%20of%206%2F12%2F2019%2C%20but%20%3CU%3E%3CSTRONG%3Edoes%20not%3C%2FSTRONG%3E%3C%2FU%3E%20work%20for%206%2F11%2F19%20or%206%2F13%2F2019.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-684013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691335%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358261%22%20target%3D%22_blank%22%3E%40WakandaTech1993%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24D%3A%24D%2CMATCH(1%2CINDEX((%24A%3A%24A%3D%24H%242)*(%24B%3A%24B%3D%24M%242)*(%24C%3A%24C%3DG%243)%2C0)%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3Eand%20I%20guess%20you%20shall%20use%20column%20C%20instead%20of%20column%20D%20as%20in%20your%20formulas%2C%20at%20least%20for%20the%20sample%20file.%20Plus%2C%20at%20least%20for%20the%20tasks%2C%20you%20have%20no%20match%20names%3A%20%22Task1%22%20and%26nbsp%3B%22Task%201%22%3B%26nbsp%3B%22Task%203%22%20and%26nbsp%3B%22Task%203%20%22%3C%2FP%3E%0A%3CP%3EResult%20is%20in%20G6%3AI6%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
WakandaTech1993
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

@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.