return the value from the first non-blank cell in column

[neveu]

hello all,
i would need help in retrieving very fast what is the value in the first non-blank cell in a column. the data is organized as pivot tabel.

i though of applying the isblank field but then i would have a lot to work as i have to apply it individually to all the columns i have [31 columns]

does anyone else have a simpler approach?

thanks in advance,
neveu

 

[njimack]

Not sure of your data layout, but something like this? (confirmed with Ctrl+Shift+Enter)

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

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(A1:A20,MATCH(TRUE,A1:A20<>"",0))

 

[neveu]

thank you very much,
it works perfectly

 

[Stu Dapples]

Control+shift+enter, not just enter:

=INDEX(A1:A20,MATCH(TRUE,A1:A20<>"",0))

Hi, I have asked this a few times but never had a response! What exactly does the ctrl/Shift/Enter combo do to Excel? I have used a curly brace expression before but not sure what it does....

Sorry to jack the thread BTW!

 

[neveu]

could you also advise how to the same thing but to return the value in last non-blank cell?

 

[Aladin Akyurek]

Hi, I have asked this a few times but never had a response! What exactly does the ctrl/Shift/Enter combo do to Excel? I have used a curly brace expression before but not sure what it does....

Sorry to jack the thread BTW!

The key combination signals Excel that the formula is of "array formula" type , not an ordinary (range) formula. Have a look at "array formulas" in Excel's Help.

 

[Stu Dapples]

Control+shift+enter, not just enter:

=INDEX(A1:A20,MATCH(TRUE,A1:A20<>"",0))

Will do! Many thanks....

 

[njimack]

could you also advise how to the same thing but to return the value in last non-blank cell?

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

(confirmed with ctrl+shift+enter)

 

[Aladin Akyurek]

could you also advise how to the same thing but to return the value in last non-blank cell?

=LOOKUP(9.99999999999999E+307,A1:A20)

if a number is expected.

Otherwise, one of:

=LOOKUP(9.99999999999999E+307,SEARCH("*",A1:A20),A1:A20)

=LOOKUP(REPT("z",255),A1:A20)