Search code examples
if-statementgoogle-sheetstransposeline-breaksnamed-ranges

Transpose two named ranges into same destinations cells with line break in between 1st and 2nd

YIn E2 my function =IF($B2=3,TRANSPOSE(Opponent3STARS)) works perfectly for me And in E7 my function =IF($B7=2,TRANSPOSE(Opponent2STARS)) also works for this named range

However in E6, I'd like to be able to use both of those to basically get what appears in E2 directly on top of what appears in E7. And in that same order, and such that it only gets transposed into the same size destination range of E6:I6.

Can someone help me figure out the proper way to construct the function for that?

Thanks!

I tried to use CHAR(10), IFS, CONCENTATE, etc., but I think I just missed what the correct format is.

1

Solution

  • You may try:

    =IF($B6=2.5,transpose(map(Opponent3STARS,Opponent2STARS,lambda(a,b,a&char(10)&b))))

    enter image description here

    UPDATED FORMULA

    =index(if(B2:B21=3,torow(Opponent3STARS),
           if(B2:B21=2.5,torow(map(Opponent3STARS,Opponent2STARS,lambda(a,b,a&char(10)&b))),
           if(B2:B21=2,torow(Opponent2STARS),
           if(B2:B21=1.5,torow(map(Opponent2STARS,Opponent1STARS,lambda(a,b,a&char(10)&b))),
           if(B2:B21=1,torow(Opponent1STARS),
           if(B2:B21=0.5,torow(map(Opponent1STARS,Opponent0STARS,lambda(a,b,a&char(10)&b))),
           if(B2:B21=0,torow(Opponent0STARS)))))))))
    • this is an arrayformula which extends automatically for the entire range E3:E21. you may need to clear the cells in the above said range of any existing formulas so that arrayformula is not blocked by them.

    enter image description here