Applying Array formula on the results of another array formula

[Usercode]

I have this formula

{=SMALL(N2:R2,{1,2,3,4,5})}

in (U2:Y2), when I try to use this formula

=IFERROR(INDEX($U2:$Y2,SMALL(IF($U2:$Y2=0,COLUMN($U2:$Y2)),COLUMN(U:U))),"")

on (AB2:AF2), It doesn't give any error or result. But I know this formula works, I used it before. Can it be the reason that I use =small() function on U2:Y2. Can you help me fix the problem? please. thanks.

 

[Eric W]

Perhaps the question is what are you trying to achieve? In decomposing your second formula, it looks like you're looking for 0 values in U2:Y2, and if you find one, you're putting it into AB2:AF2. Why not just put 0 there directly? Or did you mean to have the INDEX point to $U1:$Y1 or something?

Your likely problem is the COLUMN(U:U) at the end. That will return 21 in AB2, 22 in AC2, etc., and your array only has 5 values in it. Did you want to use COLUMNS($U:U) instead?

 

[Usercode]

I tried your suggestion COLUMNS($U:U), but it didn't make any difference. What I want to do as in this example, extract "0s" from the from U2:Y2 and them put them into "AB2:AF2". and they will be lined up in AB2:AF2.

 

[Usercode]

When I put my raw data in A1:V1 columns, and this formula

=IFERROR(INDEX($A3:$V3,SMALL(IF($A3:$V3=0,COLUMN($A3:$V3)),COLUMN(A:A))),"")

in X1:Z1 columns, it works. but when I try to do it on the results of an array formula, it doesn't work.

 

[Eric W]

You could just put this formula in AB2 and drag right:

=IF(COLUMNS($AB:AB)<=COUNTIF($U$2:$Y$2,0),0,"")

If you want to use an array formula, select the range AB2:AF2, and enter this formula:

=IFERROR(SMALL(IF($U$2:$Y$2=0,0,""),{1,2,3,4,5}),"")

and confirm with CSE.

 

[Usercode]

These formulas work, if there is a 0, it puts a 0 to AB1. But if there are two, three or four 0s, it doesn't put other zeros to the next columns. The original formula puts one 0 in the target cell, if there are two or more 0s, it puts them into the next cells in defined range.

 

[Usercode]

Sorry, my mistake. they do the job. I corrected it now. thanks a lot. I will use these formulas.

 

[Eric W]

You're welcome.