Results 1 to 7 of 7

Thread: Vlookup to return First Non-Zero Value in Array

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Posts
    77

    Default Vlookup to return First Non-Zero Value in Array

    I want to create a vlookup formula but instead of returning the first number in array, I want it to return the first number that is non-zero in the array

    EG: I want vlook to return $1 for product ABC and $3 for product DEF.

    Product | Jan | Feb | March |
    -----------------------------------------
    ABC | $0 | $1 | $2 |
    DEF | $3 | $4 | $5 |


    Is this possible perhaps with an array formula?

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118

    Default Re: Vlookup to return First Non-Zero Value in Array

    One possibility:


    Sheet3

     ABCD
    1Product Jan Feb March
    2ABC012
    3DEF345
    4    
    5Product   
    6ABC1  

    Spreadsheet Formulas
    CellFormula
    B6{=INDEX($B$2:$D$3,MATCH($A6,$A$2:$A$3,0),MIN(IF($A$2:$A$3=$A6,IF($B$2:$D$3<>0,COLUMN($B$2:$D$3)-COLUMN($B$2)+1))))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Does my a$$ look big in this picture ?

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    17,604

    Default Re: Vlookup to return First Non-Zero Value in Array

    Here's a similar way...

    B6, confirmed with CONTROL+SHIFT+ENTER:

    =INDEX($B$2:$D$3,MATCH($A6,$A$2:$A$3,0),MATCH(TRUE,INDEX($B$2:$D$3,MATCH($A6,$A$2:$A$3,0),0)>0,0))

    Actually, if you want to return the results on the same row, maybe...

    A1:E3...

    Product

    JanFebMarch
    ABC0121
    DEF3453


    E2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

    =INDEX(B2:D2,MATCH(TRUE,B2:D2>0,0))

  4. #4

    Default Re: Vlookup to return First Non-Zero Value in Array

    what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623

    Default Re: Vlookup to return First Non-Zero Value in Array

    Quote Originally Posted by fju2112 View Post
    what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
    Can you post some sample data and let us know what result you expect?
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,804

    Default Re: Vlookup to return First Non-Zero Value in Array

    Quote Originally Posted by fju2112 View Post
    what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
    Perhaps...

    =INDEX(B2:D2,MATCH(TRUE,B2:D2<>"",0))

    You need to apply control+shift+enter to the formula, that is, press down the control and shift keys at the same time while you hit the enter key. When done properly, a pair of curly braces, { and }, appear around the formula.

    If the foregoing is not what you want, try to elaborate by means of a small sample.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623

    Default Re: Vlookup to return First Non-Zero Value in Array

    Quote Originally Posted by fju2112 View Post
    what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
    Quote Originally Posted by T. Valko View Post
    Can you post some sample data and let us know what result you expect?
    Is this what you had in mind?

    Sheet1

     ABCDEF
    1_thistext__this
    2thatnone_some_that
    3___other_other
    4__stuffmore_stuff



    This formula entered in F1 and copied down:

    =INDEX(A1:D1,MATCH("*",A1:D1,0))
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com