Find entry in range that equals criteria in range and places in order.

[ozbeachbum]

Hi All,
Looking for a formula that will do the following.
If any cell in $CK70:210=3 than have the entry in $BY70:210 placed in order in BG starting at row 70 and than every second row after that.
EG: $CK80=3 and $BY80<>"" (then the entry in it Test 01) would show in BG70
$CK85=3 and $BY85<>"" (then the entry in it Test 02) would show in BG72
$CK200=3 and $BY200<>"" (then the entry in it Test 03) would show in BG74

 

[StephenCrump]

How about:

BG70: =LET(f,FILTER(BY70:BY210,(CK70:CK210=3)*(BY70:BY210<>"")),s,SEQUENCE(2*ROWS(f),,2),IF(ISEVEN(s),INDEX(f,s/2),""))

Or for anyone with 365: =TOCOL(EXPAND(FILTER(BY70:BY210,(CK70:CK210=3)*(BY70:BY210<>"")),,2,""))

 

[ozbeachbum]

Amazing, Thanks so much.
Never ceases to astound me what can be done in Excel and how knowable people are on this forum.
Cheers.