Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use array for lookup value, to return array of lookups | Excel Discussion (Misc queries) | |||
Return 3D array? | Excel Worksheet Functions | |||
Use array to return array of values | Excel Worksheet Functions | |||
Return Array with Array | Excel Worksheet Functions | |||
ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0 | Excel Worksheet Functions |