Return rightmost value in a row with conditions

Question

I have a list of values of variable length (columns go out can possibly go out to ValueN, this sheet will expand as time goes on). For instance:

Subject  Value1  Value2  Value3  Value4
Sub1     Pass    Fail    Fail
Sub2     Pass    NA
Sub3     Fail    Fail    Fail    Fail
Sub4     NA      NA      NA      NA
Sub5     NA      PASS    NA

I need to make a new column that reports the rightmost pass or fail, and only reports NA if all columns are NA (no rows will have all columns blank), such as this:

Subject  Value1  Value2  Value3  Value4  New Column
Sub1     Pass    Fail    Fail            Fail
Sub2     Pass    NA                      Pass
Sub3     Fail    Fail    Fail    Fail    Fail
Sub4     NA      NA      NA      NA      NA
Sub5     NA      Pass    NA              Pass

I found another post from awhile back that helps me get the right-most value (Excel formula to get last entry in a row), but I don't know how to modify the logic to only return NA if all values in the row are NA.

This is the formula I'm using to get the right-most value:

=LOOKUP(2,1/(B2:E2<>""),B2:E2)

I've tried several variations but cannot get the logic to only return NA if all values are NA correctly.

Can anyone help?

EDIT: I've accomplished the task with a lost of nested IF statements, but this doesn't solve the problem of having to modify the entire sheet when we add more columns. I'm looking for a robust solution, if such a solution exists.

Answer 1

Use the AGGREGATE¹ function to throw an error on blank cells and NA values while ignoring errors. The IFERROR function can cover NA values when nothng valid can be returned.

In G2 as,

=IFERROR(INDEX(B2:E2, AGGREGATE(14, 6, COLUMN(A:D)/(SIGN(LEN(B2:E2))*(B2:E2<>"NA")), 1)), "NA")

Fill down as necessary.

    

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}