0

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
1

2 Answers 2

2

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.

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

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.

Your Answer

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

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