Results 1 to 5 of 5

Thread: IF and VLOOKUP to find column header for values in range greater than zero

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF and VLOOKUP to find column header for values in range greater than zero

    I am trying to setup an IF statement using a VLOOKUP to find a value in a range greater than zero and return that column's header label. I have a list of names and I am trying to find the first column that has an actual dollar amount instead of blank and then return that column's header label.

    This is like my spreadsheet:

    Name Oranges Apples Plums Cherries
    Joe Smith $25.00
    Betsy Fine $10.00
    George Jones $5.00
    Wendy Wonder $50.00


    I want my formula to give me "Plums" for Joe Smith, "Apples" for Betsy Fine, "Oranges" for George Jones and "Cherries" for Wendy Wonder.

    Please advise - I know this is probably very simple but I haven't done this in a while so I appreciate the assistance!


    Thanks!
    Betty Raines

  2. #2
    New Member
    Join Date
    Jan 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF and VLOOKUP to find column header for values in range greater than zero

    I apologize - here is a better picture of my spreadsheet:

    Name Type Oranges Apples Plums Cherries
    Joe Smith $25.00
    Betsy Fine $10.00
    George Jones $5.00
    Wendy Wonder $50.00

    Under the heading "Type" I want to put my formula and have it return the column header or type of fruit for which the person has paid $. Please advise.

    Thanks!
    Betty Raines

  3. #3
    New Member mail2master's Avatar
    Join Date
    Jan 2019
    Location
    Delhi-NCR
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF and VLOOKUP to find column header for values in range greater than zero

    A B C D E F
    1 Name Type Oranges Apples Plums Cherries
    2 Joe Smith $25.00
    3 Betsy Fine $10.00
    4 George Jones $5.00
    5 Wendy Wonder $50.00

    Formula in B2: =INDEX($C$1:$F$1,1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+1),1))
    Confirm with Ctrl+Shift+Enter
    Last edited by mail2master; Jan 22nd, 2019 at 04:27 AM.

  4. #4
    New Member mail2master's Avatar
    Join Date
    Jan 2019
    Location
    Delhi-NCR
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF and VLOOKUP to find column header for values in range greater than zero

    Quote Originally Posted by mail2master View Post
    A B C D E F
    1 Name Type Oranges Apples Plums Cherries
    2 Joe Smith $25.00
    3 Betsy Fine $10.00
    4 George Jones $5.00
    5 Wendy Wonder $50.00

    Formula in B2: =INDEX($C$1:$F$1,1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+1),1))
    Confirm with Ctrl+Shift+Enter
    If you want to stick with VLOOKUP & IF, try this:

    =VLOOKUP($A$1,$A$1:$F$1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+3),1),0)
    Confirm with Ctrl+Shift+Enter

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF and VLOOKUP to find column header for values in range greater than zero

    Hi,

    Based on your sample, this would also work, normally entered and copied down:

    ABCDEF
    1NameTypeOrangesApplesPlumsCherries
    2Joe SmithPlums$25.00
    3Betsy FineApples$10.00
    4George JonesOranges$5.00
    5Wendy WonderCherries$50.00

    Sheet485



    Worksheet Formulas
    CellFormula
    B2=LOOKUP(2,1/C2:F2,C$1:F$1)


Some videos you may like

User Tag List

Tags for this Thread

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
  •