Lookup next non-empty cell

[Jay]

Let's say I have a range A1:T1 . How can I do a lookup which returns the
first non-empty cell, irrespective of the cell's content. And by
'first' I mean the first as if reading left-to-right - so if M1 was the
first cell in the range (left-to-right) not to be empty that value would
be returned.

Any help greatly appreciated.

Thanks,

Jason

 

[Bob Phillips]

=INDEX(A1:T1,MIN(IF(A1:T1<>"",COLUMN(A1:T1))))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jay]

Thanks Bob, I knew it'd involve INDEX and be an array fn but hadn't
considered using COLUMN.

Thanks again..you continue to be an invaluable source of help & info.

Regards,
Jason

 

[Jay]

Hi Bob,

How could that formula be amended so it does the same but on the range
A1:A20 (a column rather than row) I tried just substituting ROW fn for
COLUMN but it didn't work.

MANY thanks,

Jason

 

[Bob Phillips]

Jason,

In essence that is all that it is, that and the ranges

=INDEX(A1:A20,MIN(IF(A1:A20<>"",ROW(A1:A20))))

don't forget to array enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)