+ Reply to Thread
Results 1 to 4 of 4

Return Value based on Two Dynamic Sets of Criteria (Non-VBA)

  1. #1
    Registered User
    Join Date
    09-15-2018
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2016 Home Professional
    Posts
    2

    Return Value based on Two Dynamic Sets of Criteria (Non-VBA)

    Hi All,

    I'm looking for some assistance with a problem I'm having that involves lookups. I would prefer to use the INDEX and MATCH functions if possible.
    I need a formula solution instead of VBA. I feel like this is an easy one for someone of my excel aptitude but not sure why I'm having such a hard time with it.

    I've attached a copy of the excel file (note that it is not the full excel file. I extracted enough information just to make the situation and need clear).

    I need to place a formula in cell B2 that I can copy down that will look up the "department value" in the table on the right and return the number of tasks for that department according to the specified gateway in cell A2. Can't use VBA. If there is an easy way to use INDEX and MATCH I would prefer that.

    I tried the below formula but there were too many arguments for the function and it wouldn't work. (Note that the actual file I'm using has the lookup table on a different sheet (Lookup_Tables) than the sheet I need the formula in (CDB_Download).

    IF(C2="KMP",VLOOKUP(A2,Lookup_Tables!E4:E17,2),IF(C2="KPx",VLOOKUP(A2,Lookup_Tables!E4:E17,3)),IF(C2="KVx",VLOOKUP(A2,Lookup_Tables!E4:E17,4)),IF(C2="KEx",VLOOKUP(A2,Lookup_Tables!E4:E17,5)),IF(C2="KQx",VLOOKUP(A2,Lookup_Tables!E4:E17,6)))

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Return Value based on Two Dynamic Sets of Criteria (Non-VBA)

    Try this:

    =SUMIFS(N:N,O:O,C2,M:M,A2)

    but check out your file. There are errors in it - #REF errors all over the place, as much of the data is copied from other files, unavailable to us.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-15-2018
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2016 Home Professional
    Posts
    2

    Re: Return Value based on Two Dynamic Sets of Criteria (Non-VBA)

    Hi Glenn,

    This worked perfectly. Thank you so much for the fast response and great solution. Apologies about the #REF errors in the attachment. I neglected to paste as values when I extracted the data from the original file.

    Best Regards,
    Derrick

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Return Value based on Two Dynamic Sets of Criteria (Non-VBA)

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Sum Total Based On Four Different Sets of Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-12-2017, 04:16 PM
  2. How to remove duplicates within duplication sets based on criteria?
    By maupinsmason in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 05:21 AM
  3. [SOLVED] excel formula return dynamic list of names based on two criteria
    By deanusa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2015, 02:23 PM
  4. [SOLVED] Sum Total Based On Four Different Sets of Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-02-2014, 10:15 PM
  5. Bring in data from one tab based on two sets of criteria on a different tab
    By tommypkoch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2013, 02:00 PM
  6. Counting data sets based on multiple criteria
    By hjb0802 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2010, 10:47 AM
  7. Counting a column based on two sets of criteria
    By Cjax in forum Excel General
    Replies: 4
    Last Post: 07-23-2009, 02:40 PM

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