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 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 at 1:54
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 
    ) 
  ) 
)
4
  • Most elegant. Since this is a departure from the OP's approach, would you mind explaining the logic behind your answer [and for my own benefit too ;) ] – Tedinoz 2 days ago
  • @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 2 days ago
  • @andio Post your iferror(1/0) inquiry as a question. – doubleunary 2 days ago
  • @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 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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