# Thread: Find first non-null value in an array (97)

1. ## Find first non-null value in an array (97)

I need to find the first non-null value in an array and then take the number in the column next to it and place it into another cell for use in a formula. VLOOKUP will not work in this case. Is there an alternative method that can make use of built in formulas, or do I need VBA for this? Any code samples that you can provide will be most appreciated.

Drew

2. ## Re: Find first non-null value in an array (97)

Hi Drew,
There are probably simpler formulae than I've used in the attached, but it works! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

3. ## Re: Find first non-null value in an array (97)

In B11 enter the following ordinary formula...

=INDEX(D28,MATCH(1,INDEX(ISNUMBER(B2:B8)+0,0,1),0))

which is, by the way, an invention of mine.

4. ## Re: Find first non-null value in an array (97)

Your formula will work only if the first nonblank is a number. This will find the first non-blank regardless of text or number:
<pre>=INDEX(D28,MATCH(1,INDEX(NOT((B2:B8)="")+0,0,1),0))
</pre>

Steve

5. ## Re: Find first non-null value in an array (97)

I took B2:B8 to be of numeric type. If it's of mixed type or text, I use LEN...

=INDEX(D28,MATCH(1,INDEX((LEN(B2:B8)>0)+0,0,1),0))

but NOT is OK too.

By the way, How come you don't seem to be surprised by the formula? :-)

6. ## Re: Find first non-null value in an array (97)

It seemed like a perfectly "logical" formula (and a pretty neat trick) once I saw it. I would think of it as a variant of the ARRAY formula (which you seem to be proficient in!)

Steve

7. ## Re: Find first non-null value in an array (97)

THANKS A ZILLION to all who contributed!!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> My dilema has been solved and once again it was the great folks that participate in Woody's Lounge that came through!! I love this place!!

Drew

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•