Hi all,

I am looking to use the VLOOKUP function to caputure the contents of the
first nonblank cell. At the moment, I am capturing the first match - which
may be blank or non blank.

How do I capture non-blank cells only?

Thank you.

Clarissa.

Hi Clarissa,

With these conditions, you will need array formulas ...
Are you looking for the first non-blank value in a column or in a
row ...?

Hi Carim - in a Column.

"Carim" wrote:

Hi Clarissa,

With these conditions, you will need array formulas ...
Are you looking for the first non-blank value in a column or in a
row ...?

Ok then ...

Have a go with :

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

Instead of Enter, use Shift Control Enter keys simultaneously to input
formula

HTH

Hi,

I was looking to use VLOOkUP to match the first non-blank cell, the formula
you've kindly provided doesn't do that.
Thank you for trying anyway.

Clarissa

"Carim" wrote:

Ok then ...

Have a go with :

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

Instead of Enter, use Shift Control Enter keys simultaneously to input
formula

HTH

What does it do instead?



Clarissa wrote:

Hi,

I was looking to use VLOOkUP to match the first non-blank cell, the formula
you've kindly provided doesn't do that.
Thank you for trying anyway.

Clarissa

"Carim" wrote:

Ok then ...

Have a go with :

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

Instead of Enter, use Shift Control Enter keys simultaneously to input
formula

HTH


--

Dave Peterson

Hi Dave - nothing - it does not peform the role of a look up.

"Dave Peterson" wrote:

What does it do instead?



Clarissa wrote:

Hi,

I was looking to use VLOOkUP to match the first non-blank cell, the formula
you've kindly provided doesn't do that.
Thank you for trying anyway.

Clarissa

"Carim" wrote:

Ok then ...

Have a go with :

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

Instead of Enter, use Shift Control Enter keys simultaneously to input
formula

HTH


--

Dave Peterson

What does the formula return instead?

Maybe the cell looks empty, but contains a space character?

If you use:
=len(a1)
(change A1 to the cell that contains the formula)
what do you see?

If the value that's returned is not zero, then you have cells with spaces in
them.

You could clean up the original data (I would!) or you could use a different
formula:

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

(still array entered)


Clarissa wrote:

Hi Dave - nothing - it does not peform the role of a look up.

"Dave Peterson" wrote:

What does it do instead?



Clarissa wrote:

Hi,

I was looking to use VLOOkUP to match the first non-blank cell, the formula
you've kindly provided doesn't do that.
Thank you for trying anyway.

Clarissa

"Carim" wrote:

Ok then ...

Have a go with :

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

Instead of Enter, use Shift Control Enter keys simultaneously to input
formula

HTH


--

Dave Peterson


--

Dave Peterson