CODEWITHSUNDEEP
excelindexingexcel-formulamatch
Drew.M
Drew.M

Reputation: 47

Index Match formula issue with multiple criteria

I have an issue with Index/match formula in Excel.

I have the following formula;

INDEX('[Master 2019-20.xlsx]SM'!$G$2:$G$5000,MATCH(1,('[Master 2019-20.xlsx]SM'!$M$2:$M$5000=$C2)*('[Master 2019-20.xlsx]SM'!C$2:$C$5000=$B2)*('[Master 2019-20.xlsx]SM'!$AE$2:$AE$5000=$A2),0))

Detail around the formula..

This returns the value I require for that week if there is only one entry for that given week. the problem comes if there is more than one entry for that week? how can I show all reference's within a single week that meets the above criteria?

is there a way of populating a list that if the above criteria is met then it will show all associated references from column 'G'.

Any help with this would be appreciated. any other formula suggestions also welcome.

Thanks in advance.

Upvotes: 1

Views: 252

Answers (1)

JvdV
JvdV

Reputation: 75840

Hereby a small example:

enter image description here

Formula in F2:

=IFERROR(INDEX(A$1:A$11,SMALL(IF((B$2:B$11="A")*(C$2:C$11<10),ROW(A$2:A$11)),ROW(1:1))),"")

So here I looked for values with the following criteria:

  • Value = A under header2
  • Value is less than 10 under header 3

Note1: It's an array formula and need to be confirmed through CtrlShiftEnter

Note2: If I made no copy paste errors, that would mean you need to use: =IFERROR(INDEX('[Master 2019-20.xlsx]SM'!G$1:G$5000,SMALL(IF(('[Master 2019-20.xlsx]SM'!$M$2:$M$5000=C$2)*('[Master 2019-20.xlsx]SM'!C$2:$C$5000=B$2)*('[Master 2019-20.xlsx]SM'!AE$2:AE$5000=A$2),ROW('[Master 2019-20.xlsx]SM'!G$2:G$5000)),ROW(1:1))),"")

Upvotes: 1

Related Questions