Lookup next non-empty cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 22 Feb 2007 11:13 GMT

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

Reply to this Message

Bob Phillips - 22 Feb 2007 11:42 GMT

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

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

Signature

---
HTH

Bob

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

> 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'
[quoted text clipped - 6 lines]
>
> Jason

Reply to this Message

Jay - 22 Feb 2007 19:42 GMT

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

> =INDEX(A1:T1,MIN(IF(A1:T1<>"",COLUMN(A1:T1))))
>
> which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

Reply to this Message

Jay - 23 Feb 2007 11:50 GMT

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

> =INDEX(A1:T1,MIN(IF(A1:T1<>"",COLUMN(A1:T1))))
>
[quoted text clipped - 12 lines]
>>
>> Jason

Reply to this Message

Bob Phillips - 23 Feb 2007 16:05 GMT

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

Signature

---
HTH

Bob

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

> Hi Bob,
>
[quoted text clipped - 22 lines]
>>>
>>> Jason

Reply to this Message