First Non-Blank Cell
 

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

....

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

I assume you're considering cells that might have formulas that return
formula blanks ("") as blanks and should be ignored.

One way:

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<"",,1),0) )

--
Biff
Microsoft Excel MVP


"FrankM" wrote in message
...
I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

Unfortunately arrays don't help me. Thank you though ... looked promising.


"Mike H" wrote:

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

Hi,

The following array entered formula will do that

=INDEX(C3:C19,MATCH(TRUE,C3:C19<"",0))

Press Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

If you want the row no of first non-blank cell then paste this in a cell
=INDEX(MATCH(1,(--(A1:A100<"")),0),1)
and press CTRL-SHIFT-ENTER

If you want the value in first non-blank cell then use
=INDIRECT("A"&MATCH(1,(--(A1:A100<"")),0))
and press CTRL-SHIFT-ENTER

I am sure there will be a simpler formula...

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

First Non-Blank Cell
 

If you don't like array formulae, we can use a helper column. Say you data
is in column B, from B1 thru B1000. In A1 enter:

=IF(B1="","",1) and copy down

The first non-blank value in column B is then:

=VLOOKUP(1,A1:B1000,2,FALSE)

no arrays are needed.
--
Gary''s Student - gsnu200813


"FrankM" wrote:

Unfortunately arrays don't help me. Thank you though ... looked promising.


"Mike H" wrote:

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?