I have this sheet : https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing
Trying to get the right most value per row
Basic formula :
=FILTER(B2:2,COLUMN(B2:2)=MAX(FILTER(COLUMN(B2:2),LEN(B2:2))))
But i want to use with arrayformula().
I tried to combined any available formula i found on the net to make it work. In the 2nd sheet i put the arrayformula version, but it returns wrong value when the row is empty( I returns the row number). I want to filter it out.
it happens because the 3rd parameter of the vlookup() is return 1 which is refer to the column contains row(). I tried to 'trim' it so when i goes below 2 it should remain as 2. I was using MAX(**3rd param of vlookup**, 2)
but the max() doesn't work with arrayformula.
Also somehow , i feel the formula is less efficient.