Problem with reference cell when repeating formula down column

[Young Grasshopper]

Hello world!

I have this formula:
=LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)
This formula gives three random values from G11:U11 as long as the corresponding helping row, H1:V1, H2:V2 etc, is bigger den 0.
(Random since the values in H1:V1 is generated from fromula; =IF(LEN(G11)=0,0,RAND()

I'm trying to copy this formula down a column for about 100 rows, but the H:V ranges get a little messed up when copying the formula down. Right now it would go;
1+H1:V1,1+H2:V2,1+H3:V3
1+H2:V2, 1+H3:V3,1+H4:V4
1+H3:V3, 1+H4:V4,1+H5:V5

But need this formula to go;
(Row1) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)

(Row2) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H4:V4)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H5:V5)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H6:V6)),G12:U12)

(Row3) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H7:V7)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H8:V8)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H9:V9)),G13:U13)

I understand that this is just how excel would calculate this logically, but is there any way to control this or rewrite the code in a different way?
Or is it just better to do all 100 lines manually?

Would appreciate any help

 

[Domenic]

Let's say that you are going to enter the formula in X1, and then copy down. Try...

=LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$1:$V$1,ROWS(X$1:X1)*3-3,0))),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$2:$V$2,ROWS(X$1:X1)*3-3,0))),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$3:$V$3,ROWS(X$1:X1)*3-3,0))),G11:U11)

Alternatively, I think you can probably use the following instead...

=TEXTJOIN(" | ",FALSE,INDEX(G11:U11,RANDARRAY(,3,1,COLUMNS(G11:U11))))

Hope this helps!

 

[Young Grasshopper]

My man! Works like a dream:D

The last formula is also a big time saver, but the only problem is that it includes cells in G11:U11 that are blank, which is the sole reason i use the other formula.
Is there a quick tweak that would make the last code only include nonblanks in the range?

 

[Domenic]

Does this return the desired result in all circumstances (ie. less than 3 values available in G11:U11) ?

=LET(data,FILTER(G11:U11,LEN(G11:U11)>0,#N/A),TEXTJOIN(" | ",TRUE,INDEX(data,RANDARRAY(,MIN(3,COLUMNS(data)),1,COLUMNS(data),TRUE))))

 

[Young Grasshopper]

To make the situation any easier I need formulas that work in most versions of Excel, and FILTER is only in 365 i think?
Anyway, your first fomula works great together with my formula, so I'll just keep it like this

You're a lifesaver, Domenic! Enjoy the rest of your day!