# Grab the right most value using arrayformula 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.

• Would you please summarise your research. 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. Jul 22, 2021 at 1:54

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
)
)
)
``````
• @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 ? 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. Jul 23, 2021 at 9:13