# Return last value that isn't an error in each row

Trying to get some help with an issue I'm having.

In Google Sheets I have a data in columns A to D.

• Column A contains my original list of names.
• Columns B to D contain either a new name or an `#N/A` error.

I'd like a formula in column F that returns the last value in each row of columns A to D that is not an `#N/A` error

Sample Data with desired result in column F:

A B C D E F
1 Raw Data 1st Check 2nd Check 3rd Check Final Result
2 Kate Karen Joe `Harry` Harry
3 Audrey Audrey `Audrey` #N/A Audrey
4 Kam `Kam` #N/A #N/A Kam
5 Czar #N/A `Rio` #N/A Rio
6 `Wing` #N/A #N/A #N/A Wing
• If Joel's solution solved your problem you might consider accepting it. Sep 11, 2023 at 2:31

Assuming your explanation of the logic involved is complete, and the examples pictured are truly representative of the real spreadsheet, all of the rules can be replaced with the rule:

In column F, display the rightmost value from columns A to D which is not #N/A.

If that's sufficient, an example formula for cell F2 might resemble:

``````=INDEX(FILTER(A2:D2, LEN(IFNA(A2:D2))), COUNTA(INDEX(FILTER(A2:D2, LEN(IFNA(A2:D2))))))
``````

Then select F2, and drag the fill handle (the little circle at the lower-right of its highlighted outline) down to fill the other rows of F with formulas Sheets will automatically alter to match each row.

That formula just filters out any blanks or #N/A cells for the row, counts the remaining names with COUNTA, then uses that count to return the one at that "distance" into the name list… the name at that INDEX.

• IFNA is not necessary in the formula, LEN achieves what you want: `LEN(A2:D2)` vs. `LEN(IFNA(A2:D2))` Sep 13, 2023 at 0:51

## Another Approach

Joel's solution works. This is simply an alternative.

## Basic Formula

``````=INDEX(r,,XMATCH("*",IFNA(r),2,-1))
``````
• In the Basic Formula, replace `r` with a reference to a single row of data, such as `A2:D2`.

## Multi-Row Formula

``````=BYROW(mr, LAMBDA(r,
INDEX(r,,XMATCH("*",IFNA(r),2,-1))
``````
• Replace `mr` with a reference to multiple rows of data. e.g. `A2:D6`
• Do not replace `r` in the Multi-Row Formula. The LAMBDA function will use it as a variable to hold the current row passed in by the BYROW function.