Genius Level IF (?) Formula

Matthew Harrison

Does such a formula exist to acheive the below?

If I edit the Stock columns in Row 1, I want the Result columns to appear as Row 2 (i.e. data shifted to the left eliminating blank cells).

I'm nearly there with this formula, but it's not quite correct.

=IF([Stock1]1 <> "", [Stock1]1, IF([Stock2]1 <> "", [Stock2]1, IF([Stock3]1 <> "", [Stock3]1, IF([Stock4]1 <> "", [Stock4]1, IF([Stock5]1 <> "", [Stock5]1, IF([Stock6]1 <> "", [Stock6]1, IF([Stock7]1 <> "", [Stock7]1)))))))

 

 

 

L_123

There are several ways to solve this problem, but i would avoid if statements. They tend to get a little complicated. instead try an index collect =iferror(index(collect([Stock1]1:[Stock7]1,[Stock1]1:[Stock7]1,not(isblank(@cell))),1),"")

And change the last 1 in the formula to account for which return you want. So it should be 1-7 as you go through the results