Return First Blank Cell in Range

[Green Ninja X]

Hi Folks,

First off always appreciate the help from everyone.

Today I have cells A1 to A500 filled with conditional values that will return blank values in every cell except one.

If I want cell B1 to return the first not blank cell in Range A1 to A500 is there a way to do this?

Thank You Very Much

GNX

 

[Robert Mika]

http://www.mrexcel.com/forum/showthread.php?t=15062

 

[T. Valko]

Hi Folks,

First off always appreciate the help from everyone.

Today I have cells A1 to A500 filled with conditional values that will return blank values in every cell except one.

If I want cell B1 to return the first not blank cell in Range A1 to A500 is there a way to do this?

Thank You Very Much

GNX

Here's a generic method.

Array entered**:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[bosco_yip]

Or, this non-array formula

=INDEX(A1:A500,INDEX(MATCH(,-(A1:A500=""),),))

Regards

 

[Green Ninja X]

Thank You Bosco. That's exactly what I was looking for!