+ Reply to Thread
Results 1 to 9 of 9

VLookup with multiple criteria

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    VLookup with multiple criteria

    Hi I have a sample file attached to help with me explaining.

    I want to get the sum of the number in column C by each person in by each year.

    EX: for John I want to be able to get the sum of all the numbers in his name for every date that is in 2015, 2016 and 2017.

    Im not sure if im overthinking it, but I cant seem to figure it out.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLookup with multiple criteria

    In Cell H1 input 2015, in Cell I1 input 2016, in Cell J1 input 2017

    In Cell H2 use
    =SUMPRODUCT((YEAR($B$2:$B$11)=H$1)*($A$2:$A$11=$G2)*$C$2:$C$11)

    Copy down and across
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: VLookup with multiple criteria

    That worked for what I asked for.... unfortunately I didn't know what I was talking about at first and attached another sheet that is now what im looking for.

    I want to extract the "number" in column C that is the latest date for each "member number".
    EX: In column J3 I want to extract the number in cell C11 because its the number that is in the latest date of that specific year.

    Thank you for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: VLookup with multiple criteria

    J6* not J3.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VLookup with multiple criteria

    ARRAY formula (PL see note below) in H2, then drag down

    =IFERROR(INDEX($C$2:$C$16,MATCH(2,1/(($A$2:$A$16=$G2)*(YEAR($B$2:$B$16)=H$1)),1)),"")
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    05-25-2016
    Location
    United States, Texas
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: VLookup with multiple criteria

    This produced a blank in all of my cells.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: VLookup with multiple criteria

    Enter formula as below ...


    ..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VLookup with multiple criteria

    It is ARRAY formula. Pl see the file enclosed. Formula is working.

    To enter ARRAY formula
    Paste the formula
    Press F2
    Press Ctrl+Shift+Enter keys together.
    formula will be covered with{} brackets by excel.

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: VLookup with multiple criteria

    Here is a regular formula to get unique member list
    Enter in G2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then enter regular formula in H2 and drag formula across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G H I J
    1 Member Number Date Number Member Number 2015 2016 2017
    2 1145842 2/8/2015 85 1145842 85 97
    3 1145842 2/8/2016 43 1547851 55
    4 1145842 5/8/2016 97 1548796 102
    5 1547851 4/8/2017 55 1548795 20 96
    6 1548796 6/8/2017 55 1224587 58 232
    7 1548796 9/4/2017 102 1254785 97
    8 1548795 6/2/2016 20 1245844 33
    9 1548795 4/2/2017 96
    10 1224587 4/2/2017 105
    11 1224587 5/7/2017 232
    12 1224587 6/8/2016 11
    13 1224587 11/7/2016 58
    14 1254785 5/8/2016 25
    15 1254785 12/7/2016 97
    16 1245844 12/5/2017 33
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlookup multiple criteria returning multiple values
    By ykobure in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-16-2015, 07:28 AM
  2. [SOLVED] iserror and vlookup with multiple criteria from multiple tables along with concatenation.
    By marcusduton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-14-2015, 02:16 AM
  3. Vlookup on multiple axis with multiple criteria
    By theLOLkid in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-11-2015, 02:48 PM
  4. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  5. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  6. Possible VLOOKUP multiple criteria & multiple worksheets
    By this2willpass in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-16-2012, 08:23 AM
  7. Vlookup + multiple criteria + multiple data returned
    By stonesy in forum Excel General
    Replies: 6
    Last Post: 02-17-2010, 08:53 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1