Results 1 to 7 of 7

Vlookup to return First Non-Zero Value in Array

This is a discussion on Vlookup to return First Non-Zero Value in Array within the Excel Questions forums, part of the Question Forums category; I want to create a vlookup formula but instead of returning the first number in array, I want it to ...

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

    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,113

    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
    14,264

    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))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  4. #4
    New Member
    Join Date
    Nov 2011
    Posts
    1

    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,133

    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

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

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

    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,133

    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

    Using Excel 2002, 2007
    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