Formula to Return Positions of Values from an Array as an Array?

[MEUserII]

Consider the following array, {1, 2, 3, 4, 5, 6, 7, 8, 9}, is there a way that I can return the positions of the values in a separate array that match a certain criteria?

For example, if I wanted just the positions of the even values from this example array, {1, 2, 3, 4, 5, 6, 7, 8, 9}, the positions of the even values as an array would be: {2, 4, 6, 8}, where "{2, 4, 6, 8}" represent the positions of each of the even values in the example array as positions: second position, fourth position, sixth position, and eighth position.

Is there a way to do this via formula to do this?

More generally, taking the example array of: {0, 1, 0, 1, 0, 1, 0, 1, 0}; would there be a way via formula to return the positions of the "1" values of that array which would be the array of: {2, 4, 6, 8}; where "{2, 4, 6, 8}" represent the: second position, fourth position, sixth position, and eighth position?

For reference, I need to be able to return the positions of the values of an array as a separate array for use with the following formula:

=INDEX( RANGE, N( IF(1,{2, 4, 6, 8} ) ) )

For reference on this formula see: https://exceljet.net/formula/return-array-with-index-function

 

[Eric W]

You could do something like this:

=SMALL(IF(MOD({11,12,13,14,15,16,17,18,19},2)=0,{1,2,3,4,5,6,7,8,9}),ROW(INDIRECT("1:"&SUM(IF(MOD({11,12,13,14,15,16,17,18,19},2)=0,1)))))

But note that it requires using your array twice, plus creating another array to get the indices, which might be something like:

ROW(INDIRECT("1:"&COUNT({11,12,13,14,15,16,17,18,19}))

meaning yet a third instance of your array. I don't know what your end goal is, but I gotta think there's a better way.

 

[Domenic]

Or, if you have MODE.MULT available in your version of Excel, the following will return a vertical array of values (ie. {2;4;6;8})...

MODE.MULT(IF({0,1,0,1,0,1,0,1,0}=1,{1,2,3,4,5,6,7,8,9}*{1;1}))

To return a horizontal array of values (ie. {2,4,6,8}), try...

TRANSPOSE(MODE.MULT(IF({0,1,0,1,0,1,0,1,0}=1,{1,2,3,4,5,6,7,8,9}*{1;1})))

Hope this helps!

 

[DRSteele]

If you are using Excel365 Insider, you can use the fancy new functions and dynamic arrays. This example contains a singlecell formula in B7.

Book1
	A	B	C	D	E	F	G	H	I	J
1	an array of 1's and 0's	0	1	0	1	0	1	0	1	0
2
3	conditional array (=1's)	FALSE	2	FALSE	4	FALSE	6	FALSE	8	FALSE
4	count of numbers	4
5	position of 1's	2	4	6	8
6
7	position of 1's (singlecell formula)	2	4	6	8
Sheet6

Cell Formulas
Range	Formula
B1	={0,1,0,1,0,1,0,1,0}
B3	=IF(B1#,SEQUENCE(1,COLUMNS(B1#)))
B4	=COUNT(B3#)
B5	=AGGREGATE(15,6,B3#,SEQUENCE(1,B4))
B7	=AGGREGATE(15,6,IF(B1#,SEQUENCE(1,COLUMNS(B1#))),SEQUENCE(1,COUNT(IF(B1#,SEQUENCE(1,COLUMNS(B1#))))))

_{^{<strike>
</strike>}}