how can I return the value of the first not null cell in a column
 

I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a column
 

One way, array-entered* in say, B1:
=INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0))

*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"NHH" wrote:
I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a column
 

From your formula I guess you may be trying to find the first number. Try
this array entered.

=INDEX(A1:A1000,MATCH(TRUE,ISNUMBER(A1:A1000),0))

Mike

"NHH" wrote:

I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a col
 

Dear Max,

Thank you for your reply.

Regret to inform you that your formula returned an error "#VALUE!", it
didn't work out. Sorry.

Looking for your further effort to help me in this issue.

Best Regards,

NHH

"Max" wrote:

One way, array-entered* in say, B1:
=INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0))

*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"NHH" wrote:
I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a col
 

Dear Mike H,

Thank you for your reply.

Regret to inform you that your formula returned an error "N/A". Id didn't
work out. Sorry.

Looking for your futher effort to help me in this issue.

Best Regards,

NHH


"Mike H" wrote:

From your formula I guess you may be trying to find the first number. Try
this array entered.

=INDEX(A1:A1000,MATCH(TRUE,ISNUMBER(A1:A1000),0))

Mike

"NHH" wrote:

I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a col
 

Hi again,

Sorry, your formula did work when I pressed F2, ctrl+shift+enter

Thank you very much.

Best Regards,

NHH

"Max" wrote:

One way, array-entered* in say, B1:
=INDEX(A1:A100,MATCH(TRUE,A1:A100<"",0))

*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"NHH" wrote:
I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a col
 

Dear Mike H,

Sorry, your formula did work out when I pressed F2, ctrl+shift+enter on the
array formula.

Thanks a lot.

Best Regards,


NHH

"Mike H" wrote:

From your formula I guess you may be trying to find the first number. Try
this array entered.

=INDEX(A1:A1000,MATCH(TRUE,ISNUMBER(A1:A1000),0))

Mike

"NHH" wrote:

I need a formula to help return the value of the first not null cell in a
column, I know the formula that returns the same but in the last not null
cell in a column (LOOKUP(99^99,Sheet1!A:A)), but I need the first not the last

Thank you for your help

how can I return the value of the first not null cell in a col
 

Sorry, your formula did work when I pressed F2, ctrl+shift+enter
Glad you got it working fine over there
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---