Grab the right most value using arrayformula

Question

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.

Answer 1

You can get the column number of the rightmost value in each row by using if() expression that returns:

1. Column number when the value in the row is not blank
2. A blank value when the value in the row is blank.

Then use a query() to find the max column number in each row.

To turn these column numbers into values, use them as indices in a vlookup() expression that iterates every row number in the range against itself.

To handle blank rows, use iferror() to catch the resulting errors in vlookup(). The final formula becomes:

=arrayformula( 
  iferror( 
    vlookup( 
      row(B2:Z), 
      { row(B2:Z), B2:Z }, 
      transpose( query(
        query(
          transpose( if( len(B2:Z), column(B2:Z), iferror(1/0) )  ), 
          "select max(Col" & join("), max(Col", sequence(rows(B2:Z))) & ")", 0 
        ), 
        "offset 1", 0 
      ) ) 
      - min(column(B2:Z)) + 2, 
      false 
    ) 
  ) 
)