Find the last cell value in a non-adjacent named range

[beninoz]

I need to find the last cell value in a non-adjacent named range.
I've tried the following formula with adjacent cells and it works fine, just NOT In a non-adjacent range.
I've named the range = REVs {=MASTER!$V$4,MASTER!$AB$4,MASTER!$AH$4,MASTER!$AN$4,MASTER!$AT$4,MASTER!$AZ$4,MASTER!$BF$4,MASTER!$BL$4,MASTER!$BR$4}
Formula: =LOOKUP(2,1/(REVs<>""),REVs)

I have a cumbersome formula that does work however but I'd like to make it better:
=SINGLE(IF((SINGLE(REV_9))="",IF((SINGLE(REV_8))="",IF((SINGLE(REV_7))="",IF((SINGLE(REV_6))="",IF((SINGLE(REV_5))="",IF((SINGLE(REV_4))="",IF((SINGLE(REV_3))="",IF((SINGLE(REV_2))="",IF((SINGLE(REV_1))="","",REV_1),REV_2),REV_3),REV_4),REV_5),REV_6),REV_7),REV_8),REV_9))
Thanks!!

 

[Oaktree]

Assuming your version of Office 365 has caught up to the recent update where VSTACK was released, you can use that new function to *make* an adjacent array and envoke the same formula you were using before:

=LET(x,VSTACK(REVs),LOOKUP(2,1/(x<>""),x))

 

[beninoz]

Brilliant!! Thanks so much. I wasn't aware of the VSTACK function.

 

[beninoz]

Next question if I may:
How do I make the named range (REVs) dynamic (for the rest of the rows down - row 4, 5, 6 etc.)?
I've tried to make the columns constants and the rows dynamic but it doesn't work.
e.g. {=MASTER!$V4,MASTER!$AB4,MASTER!$AH$,MASTER!$AN4,MASTER!$AT4,MASTER!$AZ4,MASTER!$BF4,MASTER!$BL4,MASTER!$BR4}
Really appreciate your time!

 

[Fluff]

You have missed the row number from the AH col

 

[beninoz]

Thanks FLUFF
That worked.

=LET(x,VSTACK(REVs),LOOKUP(2,1/(x<>""),x))

REVs is: {=Master!$F2,Master!$L2,Master!$R2,Master!$X2,Master!$AD2,Master!$AJ2,Master!$AP2,Master!$AV2,Master!$BB2}

 

[Fluff]

Glad we could help & thanks for the feedback.