+ Reply to Thread
Results 1 to 7 of 7

Match and IF formula together for multiple conditions

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Match and IF formula together for multiple conditions

    I need to add multiple conditions and looking for a formula that summarizes the result. Please see the excel spreadsheet attached. Basically, I would like to add conditions for each scenario and get the result like C32:K38 in the attached file. I have also put the rule against each one.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Match and IF formula together for multiple conditions

    Paste into C32 and drag across/down:

    Please Login or Register  to view this content.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Match and IF formula together for multiple conditions

    Thanks for your response.
    However, this doesn't work if there is change in scenario. I have mentioned some notes on the attached file. It won't switch back and forth to apply the right formula based on the each situation. Please take a look at the attached file.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Match and IF formula together for multiple conditions

    Detailed Descriptions,
    In the table below I would like to get a summary table by applying a formula based on the several conditions.They are
    IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%External)
    IF only External and Internal are present then the formula should be (70%external+ 30% internal)
    If only External and self are present then the formula should be (70% external+30% Internal)
    If only Internal and self are present then the formula should be (30% Internal+70% self)
    If only one type of Nomination (Self, Internal or External) if present then. Apply 100% of that.
    Employee ID Nomination Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7
    1 Self 4.0 2.0 4.0 4.0
    1 Internal 2.0 2.0 4.0 3.0
    1 External 3.0 2.0 1.0 2.0
    result 3.1 2.0 2.0 2.0 1.9 2.6 3.7
    2 Self
    2.0

    4.0 3.0 5.0
    2 Internal 3.0 3.0 6.0


    2.0
    2 External
    5.0 2.0 2.0
    2.0
    result 0.3 4.2 3.2 2 4 2.3 4.1









    Employee ID Summary
    1 3.1 2 2 2 1.9 2.6 3.7
    2 0.3 4.2 3.2 2 4 2.3 4.1
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Match and IF formula together for multiple conditions

    In your workbook, you wrote 30% External within J3. Should that be 70%?

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Match and IF formula together for multiple conditions

    See attached for a possible solution.

    The most convenient way to accomplish this is most likely through a lookup table (which you can hide) since array formulas don't allow for non-contiguous cell references within them (multiplying a percentage by a cell number then adding...).

    However, it is possible, although not advised, to put the entire formula into a single IF statement; if you absolutely need this to be a self contained formula, that can happen. Just know that it will be much more difficult down the road if you ever need to alter the percentages allotted to any of the nominations.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Match and IF formula together for multiple conditions

    Works for me, Thanks a lot

+ 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. Help with Index/Match Formula-two conditions and return multiple items
    By bigermac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-08-2015, 11:06 AM
  2. [SOLVED] lookup and match with multiple conditions
    By RobertM01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2015, 07:46 PM
  3. Replies: 2
    Last Post: 01-26-2015, 04:53 AM
  4. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  5. IF formula with multiple conditions using INDEX/MATCH
    By cad1llac in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-05-2013, 06:32 AM
  6. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  7. Index + Multiple Match Conditions??
    By Spellbound in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-01-2007, 04:07 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