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?

 

[jasonb75]

As you're using 365, you can use the much simpler FILTER function instead.

=FILTER(Assignments!$B$6:$B$336,(Assignments!$B$6:$B$336)*(Assignments $I$6:$I$336>=1),"")

Your INDEX method is unconventional but I think that I have translated it correctly.

 

[NorthbyNorthwest]

Hi, Jason. Thanks for response. I think formula does not take into account I need column B to equal a named range (QAS) in addition to column I equal to or greater than 1. Also, is there an alternative to filter? The formula is in a table. The formula I was using is CSE formula. I don't know what else to do.

 

[jasonb75]

Sorry, I thought I had included the named range. I must have missed it when I copied from your post.

=FILTER(Assignments!$B$6:$B$336,(QAS=Assignments!$B$6:$B$336)*(Assignments $I$6:$I$336>=1),"")

An alternative without FILTER (does not need CSE). Note that the last part, B$2:B2 should refer to the cell that contains the first formula. For example, if you enter the formula into J5 then it should be changed to J$5:J5. Whilst this is not strictly essential for the formula to work, it does prevent errors if rows are inserted or deleted.

=IFERROR(INDEX(Assignments!$B:$B,AGGREGATE(15,6,ROW(Assignments!$B$6:$B$336)/(QAS=Assignments!$B$6:$B$336)/(Assignments $I$6:$I$336>=1),ROWS(B$2:B2))),"")

As you're using it in a table, the ROWS() counter can still cause problems (I've seen it a few times but don't remember the exact cause, I've stopped using tables now because they cause more problems than they solve). This is the same formula as above but with a slight change to be more reliable in a table. In the last 2 parts, Table1 should be changed to the name of your actual table. [@Name] and [Name] should be the name / column heading of the formula column.

=IFERROR(INDEX(Assignments!$B:$B,AGGREGATE(15,6,ROW(Assignments!$B$6:$B$336)/(QAS=Assignments!$B$6:$B$336)/(Assignments $I$6:$I$336>=1),ROW([@Name])-ROW(Table1[[#Headers],[Name]]))),"")

 

[NorthbyNorthwest]

=FILTER(Assignments!$B$6:$B$336,(Assignments!$B$6:$B$336)*(Assignments!$E$6:$E$336=QAS)*(Assignments $I$6:$I$336>=1),"")

Sorry, I thought I had included the named range. I must have missed it when I copied from your post.

=FILTER(Assignments!$B$6:$B$336,(QAS=Assignments!$B$6:$B$336)*(Assignments $I$6:$I$336>=1),"")

An alternative without FILTER (does not need CSE). Note that the last part, B$2:B2 should refer to the cell that contains the first formula. For example, if you enter the formula into J5 then it should be changed to J$5:J5. Whilst this is not strictly essential for the formula to work, it does prevent errors if rows are inserted or deleted.

=IFERROR(INDEX(Assignments!$B:$B,AGGREGATE(15,6,ROW(Assignments!$B$6:$B$336)/(QAS=Assignments!$B$6:$B$336)/(Assignments $I$6:$I$336>=1),ROWS(B$2:B2))),"")

As you're using it in a table, the ROWS() counter can still cause problems (I've seen it a few times but don't remember the exact cause, I've stopped using tables now because they cause more problems than they solve). This is the same formula as above but with a slight change to be more reliable in a table. In the last 2 parts, Table1 should be changed to the name of your actual table. [@Name] and [Name] should be the name / column heading of the formula column.

=IFERROR(INDEX(Assignments!$B:$B,AGGREGATE(15,6,ROW(Assignments!$B$6:$B$336)/(QAS=Assignments!$B$6:$B$336)/(Assignments $I$6:$I$336>=1),ROW([@Name])-ROW(Table1[[#Headers],[Name]]))),"")

Thanks so much for filter solution, Jason. I decided there was no reason not to convert table to range.

 

[jasonb75]

You're welcome, glad to help and thank you for the feedback

I decided there was no reason not to convert table to range.

Beyond the banding making it a little easier to follow rows visually when you have a lot of very similar entries, I don't think I've actually found a good use for tables. From opinions that I've seen on the forum, I would say that many of the more experienced users have a dislike for them, especially when it comes to dynamic array functions.

 

[NorthbyNorthwest]

You're welcome, glad to help and thank you for the feedback

Beyond the banding making it a little easier to follow rows visually when you have a lot of very similar entries, I don't think I've actually found a good use for tables. From opinions that I've seen on the forum, I would say that many of the more experienced users have a dislike for them, especially when it comes to dynamic array functions.

One more question: Shouldn't the following also work?

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

As you can see, I'm using same criteria as before, except I now want to consider columns F, G, and H instead of column I. I'm getting a spill error.

 

[jasonb75]

Everything looks correct. Usually a spill error means that there is something in the way (if there are 50 rows that meet the criteria then the formula needs 50 empty rows to spill into).

If you select the cell with the spill error then it should show a broken border around the range that it wants to spill to, check that range for any index formulas that you may have used previously.

 

[NorthbyNorthwest]

Everything looks correct. Usually a spill error means that there is something in the way (if there are 50 rows that meet the criteria then the formula needs 50 empty rows to spill into).

If you select the cell with the spill error then it should show a broken border around the range that it wants to spill to, check that range for any index formulas that you may have used previously.

I cleared out the entire range as a test. Now the formula is returning nothing when in fact there are values greater than 1 in column F. I need the formula to return names in there are values in F or G or H or any combination of the three. I thought the plus sign equates to "or" and asterisk equates to "and." Is there a reason I cannot use the plus sign to reference all three columns?

 

[jasonb75]

I thought the plus sign equates to "or" and asterisk equates to "and."

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?