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

Topic

New Reply

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

Reply | Quote

Replies

Hi Drew,
There are probably simpler formulae than I’ve used in the attached, but it works!

Reply | Quote

In B11 enter the following ordinary formula…

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

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

Aladin

Reply | Quote

Your formula will work only if the first nonblank is a number. This will find the first non-blank regardless of text or number:

=INDEX(D2:D8,MATCH(1,INDEX(NOT((B2:B8)="")+0,0,1),0))

Steve

Reply | Quote

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

=INDEX(D2:D8,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?

Aladin

Reply | Quote

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

Reply | Quote

THANKS A ZILLION to all who contributed!!! 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

Reply | Quote