Index Match formula issue with multiple criteria

Question

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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Hereby a small example:

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 27, 2019 at 12:33

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drew.M Over a year ago

Thank you! this worked perfectly for what I required.

Collectives™ on Stack Overflow

Index Match formula issue with multiple criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related