Array Formula : IF not giving TRUE/FALSE results

[VinayShankam]

In the attached file in the 'Overdue List' tab I am trying to list all the projects that are overdue. File URL https://www.dropbox.com/s/28atfuxvwe5xstl/Test.xlsx

I get the count of over due projects from =COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150") - This is working fine

But when I try to generate a list using this array formula

=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60,">=6", IF('Master List'!$G$2:$G$60,"<150", ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

IF conditions IF('Master List'!$G$2:$G$60,">=6" and IF('Master List'!$G$2:$G$60,"<150" dont seem to generate TRUE or FALSE result. Hence I am getting a #NUM! error

Please advise what am I doing wrong

 

[Aladin Akyurek]

In the attached file in the 'Overdue List' tab I am trying to list all the projects that are overdue. File URL https://www.dropbox.com/s/28atfuxvwe5xstl/Test.xlsx

I get the count of over due projects from =COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150") - This is working fine

But when I try to generate a list using this array formula

=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60,">=6", IF('Master List'!$G$2:$G$60,"<150", ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

IF conditions IF('Master List'!$G$2:$G$60,">=6" and IF('Master List'!$G$2:$G$60,"<150" dont seem to generate TRUE or FALSE result. Hence I am getting a #NUM! error

Please advise what am I doing wrong

Control+shift+enter, not just enter:

=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,
  SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, 
  ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

If so desired, you can replace the IF(ROWS(A$2:A2)<=$J$1 bit with IFERROR.

 

[VinayShankam]

Yes I have tried Control+shift+enter, not just enter. Still get #NUM! error

 

[Aladin Akyurek]

Yes I have tried Control+shift+enter, not just enter. Still get #NUM! error

Master List houses the relevant data...

KAD			6
LAD			200
FAD			120
NAD			15
XAD			400
ZAD			4

<tbody>
</tbody>

Sheet1 houses the processing...

Result

KAD

FAD

NAD

<tbody>
</tbody>

A2, control+shift+enter, not just enter:

=IFERROR(INDEX('Master List'!$D$2:$D$60,
  SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,
  ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

This is essentially the same formula. I only removed the reference to the range in column F. Also replaced the the initial ROWS bit wit IFERROR. But all this has nothing to do with the NUM error you seem to have.

 

[syedbokhari]

Control+shift+enter, not just enter:

Question Aladin

Why do you CTRL+Shift+Enter ?

What does that initiate and should one do it always?

And when should one CTRL+Shift+Enter?

 

[Aladin Akyurek]

Question Aladin

Why do you CTRL+Shift+Enter ?

What does that initiate and should one do it always?

And when should one CTRL+Shift+Enter?

Whenever you need multiple results like in

A2:A10="kad"

which leads to a set of evaluations (not a single value) and
the function you use is not of array-processing type such as LOOKUP, FREQUENCY, or SUMPRODUCT.

 

[VinayShankam]

Please could you recheck your formula,

=IFERROR(INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),""</pre>it is throwing error.

Also do we not need ROWS(A$2:A2)<=COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150")

 

[Aladin Akyurek]

Please could you recheck your formula,

=IFERROR(INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),""it is throwing error.

Also do we not need ROWS(A$2:A2)<=COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150")

It is either:

=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

or, with J1 housing:

=COUNTIFS('Master List'!G2:G60,">=6",'Master List'!G2:G60,"<150")

=IF(ROWS($A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Whichever is chosen, the formula goes in A2 on a destination sheet and pulls of data from sheet Master List.

 

[VinayShankam]

You are a super star!

I like your better
=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Many Thanks

 

[Aladin Akyurek]

You are a super star!

I like your better
=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Many Thanks

You are welcome.