Excel formula fill range formula with dynamic result

Question

A1 has formula&nbsp;={"dog","cat"}, which filled B1 with "cat".&nbsp;In A3 the formula is =CHOOSE({1,2},A1,B1), and it worked. My goal is to fill multiple cells with one cell and need dynamic result. So this one meet my goal, but I feel CHOOSE is&nbsp;redundant and there should be a better way.&nbsp;So I write A5 as&nbsp;={A1,B1}, but Excel does not recognize this formula. What did I do wrong here? Any suggestions with good grammar?&nbsp;Thank you all for help.

sergeibaklan · Answer

qazzzlyt&nbsp;
Perhaps
=$A$1#

qazzzlyt · Answer

Oh thanks again Sergei. How about if A1 and B1 are not in a spilled range?

sergeibaklan · Answer

qazzzlyt&nbsp;
=A1:B1
shall work both for spill and range

peterbartholomew1 · Answer

qazzzlyt&nbsp;At the risk of muddling up your cats and dogs= IF({0,1},"dog","cat")&nbsp;is also a standard solution.&nbsp; Of course. if you are up for a bit of excitement, you could always try= LET(
    select, RANDARRAY(1,2,0,1,1),
    IF(select, "dog","cat"))

Forum Discussion

Excel formula fill range formula with dynamic result

4 Replies

Resources