Retrieving first non-blank cell during a V-LOOKUP
 

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.

Retrieving first non-blank cell during a V-LOOKUP
 

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 ...?

Retrieving first non-blank cell during a V-LOOKUP
 

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 ...?

Retrieving first non-blank cell during a V-LOOKUP
 

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

Retrieving first non-blank cell during a V-LOOKUP
 

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

Retrieving first non-blank cell during a V-LOOKUP
 

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

Retrieving first non-blank cell during a V-LOOKUP
 

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

Retrieving first non-blank cell during a V-LOOKUP
 

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