Adding another condition to an INDEX SMALL array

Hi - I need to add in another condition to the following formula -

{=IF(K2="None Required","None Required",IF(K2="","",INDEX('99-115-10'!$E$2:$E$700,SMALL(IF((K2='99-115-10'!$L$2:$L$700)*(COUNTIF(Matrix!$H$1:L1,'99-115-10'!$E$2:$E$700)=0),ROW('99-115-10'!$L$2:$L$700)-MIN(ROW('99-115-10'!$L$2:$L$700))+1,""),1))))}

Essentially in addition to the other conditions I want it to only return the values IF('99-115-10'!$F$2:$F$700="" but I can't get the syntax right. I thought it might be something like -
{=IF(K2="None Required","None Required",IF(K2="","",INDEX('99-115-10'!$E$2:$E$700,SMALL((IF('99-115-10'!$F$2:$F$700="")*if(((K2='99-115-10'!$L$2:$L$700)*(COUNTIF(Matrix!$H$1:L1,'99-115-10'!$E$2:$E$700)=0),ROW('99-115-10'!$L$2:$L$700)-MIN(ROW('99-115-10'!$L$2:$L$700))+1,""),1))))}

However, sadly that doesn't work - bad syntax. Can anyone help me tidy this formula up?

Also - first post, whoop.

Try:

=IF(K2="None Required","None Required",IF(K2="","",INDEX('99-115-10'!$E$2:$E$700,SMALL(IF((K2='99-115-10'!$L$2:$L$700)*(COUNTIF(Matrix!$H$1:L1,'99-115-10'!$E$2:$E$700)=0)*('99-115-10'!$F$2:$F$700=""),ROW('99-115-10'!$L$2:$L$700)-MIN(ROW('99-115-10'!$L$2:$L$700))+1,""),1))))

You were already multiplying two conditions together... you just need to multiply the new condition... multiplying conditions together is like nesting IFs.


How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save