Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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>
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Aladin
    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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? :-)

    Aladin
    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •