Can the "Lookup" formula be used to find separate values instead of a range?

[RockandGrohl]

I found this formula which returns the rightmost non-blank cell in a range:

=LOOKUP(2,1/(P7:S7<>""),P7:S7)

Can I instead specify, like... M7, O7, Q7, S7?


Cheers.

 

[JackDanIce]

Not sure what your layout is but maybe use an override cell i.e.

A1: =LOOKUP(2,1/(P7:S7<>""),P7:S7)
B1: =M7
C1: =IF(LEN(B1)>0,B1,A1)

Delete B1 if you want C1 to show A1, else enter specific into B1

 

[RockandGrohl]

Hi Jack, thanks for the reply.

The lookup range is H:S, however the only cells I want to get the right-most value for are H, K, N and Q - Every cell with 2 cells in between.

 

[steve the fish]

Because you have a pattern you could use this:

=LOOKUP(2,1/((M7:S7<>"")*(ISODD(COLUMN(M7:S7)))),M7:S7)