Results 1 to 6 of 6

Thread: Help needed - VLOOKUP based on IF statement, or similar
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2005
    Posts
    276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help needed - VLOOKUP based on IF statement, or similar

    Hi there,
    I have a data set and Im trying to look up a specific value based on an initial look up in one column and then a secondary look up in another.

    So data would look something like this:

    A B C
    Apples Granny Smyth 20
    Apples Cooking 10
    Apples Pink Lady 15


    So based on the initial lookup of Apples, then a second lookup of Cooking to give me the total number.

    Any help appreciated, thanks.

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Help needed - VLOOKUP based on IF statement, or similar

    If you want a total sum or there is only one entry of each pair then:

    =SUMIFS(C:C,A:A,"Apples",B:B,"Cooking")

    Or if you just want the first hit:

    =INDEX($C$1:$C$100,MATCH(1,INDEX(($A$1:$A$100="Apples")*($B$1:$B$100="Cooking"),0),0))

    Or the last hit:

    =LOOKUP(2,1/(($A$1:$A$100="Apples")*($B$1:$B$100="Cooking")),$C$1:$C$100)

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Help needed - VLOOKUP based on IF statement, or similar

    one way

    ABC
    1ApplesGranny Smyth20
    2ApplesCooking10
    3ApplesPink Lady15
    4
    5
    6ApplesCooking10

    Sheet14



    Worksheet Formulas
    CellFormula
    C6=SUMPRODUCT(--(A1:A3=A6),--(B1:B3=B6),C1:C3)


  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,188
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help needed - VLOOKUP based on IF statement, or similar

    =INDEX(C$1:C$3,MATCH(A6,IF(A$1:A$3=A$5,B$1:B$3),0),1)
    Array formula, use Ctrl-Shift-Enter

    Where A5 is Apples
    and A6 is Cooking

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,199
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help needed - VLOOKUP based on IF statement, or similar

    Something like
    Excel 2013/2016
    ABCDE
    2ApplesGranny Smith20Apples
    3ApplesCooking10Cooking
    4ApplesBramley1510

    Names



    Array Formulas
    CellFormula
    E4{=INDEX(C2:C4,MATCH(E2&"|"&E3,A2:A4&"|"&B2:B4,0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Oct 2005
    Posts
    276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help needed - VLOOKUP based on IF statement, or similar

    Brilliant, thanks so much. Appreciate the help.

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
  •