How to return multiple values using mulitple criteria in Excel

Question

I am trying to return multiple values using multiple criteria...below is the code I have tried (but using the special brackets) but it keeps coming back as though I am trying to create a sum and have an error...

=IFERROR(INDEX(Sheet2!$C$4:$C$25,SMALL((IF(Sheet2!$D$4:$D$41=Sheet3!G2)*(Sheet2!$E$4:$E$67=Sheet3!$L$3),ROW(Sheet2!C4:C24)),ROW(1:1))),"")

Below is a code that returns one result, but there are multiple names for the two matching criteria.

=IFERROR(INDEX(Sheet2!$C$4:$E$43,MATCH(1,(Sheet2!$D$4:$D$26=Sheet3!$G$2)*(Sheet2!$E$4:$E$26=Sheet3!$L$3),0),1)," ")

essentially, I have a stakeholder analysis square - broken down into:

• Keep Satisfied (Sheet 3: cell C2)
• Manage closely (Sheet 3: cell G2)
• Keep Informed (Sheet 3: cell C13)
• Monitor (Sheet 3: cell GG13)

I am using a data validation in sheet 3: cell L11 (linked to Sheet 2), to create the second criteria (project).

What I would like to happen is for it to return the names into each of the stakeholder analysis headings. The problem is, if for example, I have 3 names in 'manage closely' for one project - I cannot get it to return all three names...only the one.

This has been driving me insane

Answer 1

There are several errors in the first formula you give, i.e.:

=IFERROR(INDEX(Sheet2!$C$4:$C$25,SMALL((IF(Sheet2!$D$4:$D$41=Sheet3!G2)*(Sheet2!$E$4:$E$67=Sheet3!$L$3),ROW(Sheet2!C4:C24)),ROW(1:1))),"")

For one, the ranges passed should be of an identical size; in your case, all four are different, viz:

Sheet2!$C$4:$C$25: 22 rows

Sheet2!$D$4:$D$41: 38 rows

Sheet2!$E$4:$E$67: 64 rows

Sheet2!C4:C24: 21 rows

I can only guess at what should be the correct range, so let's assume the largest for the sake of argument.

Secondly, I assume that the reference:

Sheet3!G2

should actually be absolute, i.e.

Sheet3!$G$2

so as to be invariable as this formula is copied down?

I will also make a few other corrections, to the value_if_true clause being used for the IF statement (the one you are currently using is not correct), and also to SMALL's k parameter (ROWS is more robust than ROW here: https://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/).

All in all:

=IFERROR(INDEX(Sheet2!$C:$C,SMALL(IF(Sheet2!$D$4:$D$67=Sheet3!$G$2,IF(Sheet2!$E$4:$E$67=Sheet3!$L$3,ROW(Sheet2!$C$4:$C$67))),ROWS($1:1))),"")

and copied down.

You might also want to take note that, if the ranges being questioned are in fact quite large, the use of IFERROR can here be very inefficient:

https://superuser.com/questions/812727/look-up-a-value-in-a-list-and-return-all-multiple-corresponding-values/812848

Regards