0

I have this sheet : https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing

enter image description here

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.

2
  • Would you please summarise your research.
    – Tedinoz
    Jul 21, 2021 at 1:15
  • i've added the arrayformula in 2nd sheet. It works but when row is empty it returns the row number. The detail in my post.
    – andio
    Jul 22, 2021 at 1:54

1 Answer 1

2

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 
    ) 
  ) 
)
3
  • @doubleunary , i revisit this and find a question about it : why do you use iferror(1/0). Can we just replace it with "" like this : if( len(B2:Z), column(B2:Z), "" ). Any special purpose of that iferror() ? or in what case it will be useful ?
    – andio
    Jul 23, 2021 at 3:16
  • @andio Post your iferror(1/0) inquiry as a question. Jul 23, 2021 at 7:31
  • @doubleunary I've just got the answered. It returns trully blank cell and won't be counted when using formula like counta(). Anyway thanks a lot for your script.
    – andio
    Jul 23, 2021 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.