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 :
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.