Index Match formula issue with multiple criteria

1
votes

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

  • Index is column 'G' which is a list of reference numbers.
  • first match is from column 'M' which is a product description.
  • second match is from column 'C' which is the week number.
  • third match is a count column 'AE' which is looking for '1'.

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.

1
excelindexingexcel-formulamatch

1 Answers

1
votes

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))),"")