Need to return an array
 

I am using this typical array-entered construct in "copy down" mode to
generate a list:

INDEX('All Data'!$B$4:$B$1504,SMALL(IF(AND('All Data'!$X$4:$X$1504=C$37,'All
Data'!$A$4:$A$1504=$C$3),ROW('All Data'!$B$4:$B$1504)-ROW('All
Data'!$I$4)+1),ROWS(C$40:C40)))

I've had to introduce an AND function to test for two conditions inside the
IF statement. I realize AND is not going to return an array, even with the
whole function being array-entered. Is there something I can use instead of
AND to produce a Boolean array: TRUE if both pairs test true, FALSE otherwise?

TIA

Need to return an array
 

Multiplicative AND. Instead of:

AND('All Data'!$X$4:$X$1504=C$37,'All Data'!$A$4:$A$1504=$C$3)

try it this way:

('All Data'!$X$4:$X$1504=C$37)*('All Data'!$A$4:$A$1504=$C$3)

Hope this helps.

Pete

On Sep 10, 1:19*am, andy62 wrote:
I am using this typical array-entered construct in "copy down" mode to
generate a list:

INDEX('All Data'!$B$4:$B$1504,SMALL(IF(AND('All Data'!$X$4:$X$1504=C$37,'All
Data'!$A$4:$A$1504=$C$3),ROW('All Data'!$B$4:$B$1504)-ROW('All
Data'!$I$4)+1),ROWS(C$40:C40)))

I've had to introduce an AND function to test for two conditions inside the
IF statement. *I realize AND is not going to return an array, even with the
whole function being array-entered. *Is there something I can use instead of
AND to produce a Boolean array: TRUE if both pairs test true, FALSE otherwise?

TIA

Need to return an array
 

Perfect, thanks!

"Pete_UK" wrote:

Multiplicative AND. Instead of:

AND('All Data'!$X$4:$X$1504=C$37,'All Data'!$A$4:$A$1504=$C$3)

try it this way:

('All Data'!$X$4:$X$1504=C$37)*('All Data'!$A$4:$A$1504=$C$3)

Hope this helps.

Pete

On Sep 10, 1:19 am, andy62 wrote:
I am using this typical array-entered construct in "copy down" mode to
generate a list:

INDEX('All Data'!$B$4:$B$1504,SMALL(IF(AND('All Data'!$X$4:$X$1504=C$37,'All
Data'!$A$4:$A$1504=$C$3),ROW('All Data'!$B$4:$B$1504)-ROW('All
Data'!$I$4)+1),ROWS(C$40:C40)))

I've had to introduce an AND function to test for two conditions inside the
IF statement. I realize AND is not going to return an array, even with the
whole function being array-entered. Is there something I can use instead of
AND to produce a Boolean array: TRUE if both pairs test true, FALSE otherwise?

TIA

Need to return an array
 

You're welcome, Andy - thanks for feeding back.

Pete

On Sep 10, 2:40*am, andy62 wrote:
Perfect, thanks!



"Pete_UK" wrote:
Multiplicative AND. Instead of:

AND('All Data'!$X$4:$X$1504=C$37,'All Data'!$A$4:$A$1504=$C$3)

try it this way:

('All Data'!$X$4:$X$1504=C$37)*('All Data'!$A$4:$A$1504=$C$3)

Hope this helps.

Pete

On Sep 10, 1:19 am, andy62 wrote:
I am using this typical array-entered construct in "copy down" mode to
generate a list:

INDEX('All Data'!$B$4:$B$1504,SMALL(IF(AND('All Data'!$X$4:$X$1504=C$37,'All
Data'!$A$4:$A$1504=$C$3),ROW('All Data'!$B$4:$B$1504)-ROW('All
Data'!$I$4)+1),ROWS(C$40:C40)))

I've had to introduce an AND function to test for two conditions inside the
IF statement. *I realize AND is not going to return an array, even with the
whole function being array-entered. *Is there something I can use instead of
AND to produce a Boolean array: TRUE if both pairs test true, FALSE otherwise?

TIA- Hide quoted text -

- Show quoted text -