Formula to check is cells are blank. If the cell is not blank then assign a value

Question

I have a data set on Google Sheets where for example,

Name	Stage 1 Date	Stage 2 Date	Stage 3 Date	Stage 4 Date
Bob	3/1/2023	3/5/2023	3/31/2023	4/15/2023
Joe	2/1/2023
Zoe	3/21/2023		4/1/2023	4/15/2023
Sam	3/15/2023		4/1/2023

I am looking for a formula to return a value (Ex. 4) for the last date a person was in.

Ex Scenario 1 - The formula should return "4" for Bob.

Ex Scenario 2 - The formula should return "1" for Joe.

Ex Scenario 3 - The formula should return "4" for Zoe.

Ex Scenario 4 - The formula should return "3" for Sam.

Any help would be appreciated!

Answer 1

Assuming you have your table in A1:E5, try this out in row 2 of an empty column.

=ARRAYFORMULA(IFNA(BYROW(B2:E,LAMBDA(row,MATCH(,0/(row<>""))))))