Multiple Conditions Formula Index, Match, Small

[NorthbyNorthwest]

Hi, everyone. I have formula:

=INDEX(Assignments!$B$6:$B$336,SMALL(IF(QAS=Assignments!$E$6:$E$336,MATCH(ROW(Assignments!$E$6:$E$336),ROW(Assignments!$E$6:$E$336)),""),ROWS($A$1:A1)))

that returns a list of names to me. But now I need to add another if condition. Second condition is if Assignments $I$6:$I$336 greater than or equal to 1. I keep trying to include this condition in formula, but I come up with errors. Could someone help me?

 

[NorthbyNorthwest]

Your logic is correct and the formula should work. People often miss the extra pair of parentheses around the + arrays which can cause errors but you have it exactly as it should be.
The formulas is written correctly to return results where column E of assignments matches E2 on the dashboard and there is a value >=0 in any of the 3 columns, F, G, or H on the same row.

A simple trick that I use when formulas are not behaving correctly is to limit the ranges to a single row that I know should be a match then use the evaluation tool to see where it is going wrong. If you can find which of the columns is returning FALSE instead of TRUE then it will help in identifying the cause, perhaps something as simple as a typo in column E?

Hi, Jason. I solved it. Turns out I did not need the additional paratheses. The following formula returned the correct answer:

=FILTER(Assignments!$B$6:$B$336,(Dashboard!$E$2=Assignments!$E$6:$E$336)*(Assignments!$F$6:$F$336>=1)+(Assignments!$G$6:$G$336>=1)+( Assignments!$H$6:$H$336>=1),"")

 

[jasonb75]

Maybe I was misunderstanding what you wanted with that formula.

Without the extra parentheses it will return rows where G or H is greater than 1 even if column E is not a match.

If that is not what you want but the formula is returning the correct results then it suggests that there is something wrong with the data causing some rows to match incorrectly and that the correct results are coming through by chance rather than accuracy.

 

[NorthbyNorthwest]

Your logic is correct and the formula should work. People often miss the extra pair of parentheses around the + arrays which can cause errors but you have it exactly as it should be.
The formulas is written correctly to return results where column E of assignments matches E2 on the dashboard and there is a value >=0 in any of the 3 columns, F, G, or H on the same row.

A simple trick that I use when formulas are not behaving correctly is to limit the ranges to a single row that I know should be a match then use the evaluation tool to see where it is going wrong. If you can find which of the columns is returning FALSE instead of TRUE then it will help in identifying the cause, perhaps something as simple as a typo in column E?

You're right. I now have it working with the parentheses enclosing the "or" criteria. I guess it's me. I haven't mastered the new array functions yet. Thanks, again.