Multiple IF Conditions / Outputs
Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Multiple IF Conditions / Outputs

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple IF Conditions / Outputs

     
    Afternoon all,

    Can anyone help me with this - I've had a hunt around and can't find anything on this so I hope someone can help me.

    I have a list of 10 conditions that could be entered into a cell (G210 in this case). What I need to do is to enter into cell AJ210 a formula that checks G210 and returns the result in AJ210, based on what it finds.....e.g:

    If G210 = Condition 1, 2 or 3, 4, 5, enter into AJ210 result 1.
    or...
    If G210 = Condition 6, 7, or 8, enter into AJ210 result 2.
    or...
    If G210 = Condition 9, or 10, enter into AJ210 result 3.

    (I then need to count the resultant conditions, but that's another story....)

    I tried to use something along these lines:
    =IF(C210="","",IF(OR(G210="Condition 1",G210="Condition 2"),"Result 1","Result 2"),IF(OR(G210="Condition 6",G210="Condition 7",G210="Condition 8"),"Result 3","Result 1"))
    ...but I get too many arguments error.

    Can anyone set me on the correct path?

    Many thanks.
    Jeremy.

  2. #2
    Board Regular the_original_invisible's Avatar
    Join Date
    May 2004
    Location
    Swindon, UK
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Are you familiar with the VLOOKUP function? I think you'll find that would be a much better option for you

    Lee

  3. #3
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Hi,

    You have too many conditions, as the error says.

    Can you use a vlookup here instead? Put the range of cell values and required results into a table elsewhere, then use =vlookup to get the required result?

    Do a quick search and shout if you need anymore help!

    Bob
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, Dailyramblings.com

  4. #4
    New Member
    Join Date
    Nov 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Afternoon chaps,

    Unfortunately, I don't have any extra space or additional sheets to add a table of values because space on this sheet is very tightly controlled by others, otherwise VLOOKUP would definately be the way to go.

    Are there any other options for this, or do I need to negotiate with the Information Manager?

    Thanks for the replies so far.....
    Cheers.

  5. #5
    Board Regular West Man's Avatar
    Join Date
    Mar 2006
    Location
    Nebraska
    Posts
    1,167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Try including your table as part of the formula like:
    =LOOKUP(A4,{1,2,3,4,5,6,7,8,9,10},{"a","b","c","d","e","f","g","h","I","k"})

  6. #6
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Or if you want to stick with the conditions, can you use a range? Like 1-4 = x, 5-7 = y, 8-10 = z? That would limit your # of conditions. Like the other say, you're better off to use the lookup functions.
    -bs0d | AllSyntax

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    Madison, WI
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    try: =IF(OR(G210=Condition 1,G210=Condition 2,G210=Condition 3,G210=Condition 4,G210=Condition 5),"Result 1",IF(OR(G210=Condition 6,G210=Condition 7,G210=Condition 8),"Result 2",IF(OR(G210=Condition 9,G210=Condition 10),"Result 3","")))

  8. #8
    New Member
    Join Date
    Nov 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

    Awesome! My spreadsheet is a thing of beauty - that works a treat.

    If I could post it I would.....I used various different combinations of this to get the desired result:

    =IF(OR(G210=Condition 1,G210=Condition 2,G210=Condition 3,G210=Condition 4,G210=Condition 5),"Result 1",IF(OR(G210=Condition 6,G210=Condition 7,G210=Condition 8),"Result

    2",IF(OR(G210=Condition 9,G210=Condition 10),"Result 3","")))

    Many thanks to all those who offered help and support with this one - consider this one answered!

    (This forum rules - am now officially Excel guru in the company thanks to you guys and girls!)

  9. #9
    New Member
    Join Date
    Dec 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple IF Conditions / Outputs

      
    Hi, Above example was a good one, but how it works if I have something like below scenario?

    I want an If condition where Part A & Part B tax should be calculated based on payment date using > <, I have mentioned tax rates, request someone to help me. Thanks in Advance.

    From Date To Date Part A Tax Rate Part B Tax Rate Payment Date Payment Amount
    01 July 2010 31 March 2012 2.575 10.3 1/10/2013 200000
    01 April 2012 28 February 2013 3.09 12.36 12/1/2015 1500000
    01 March 2013 31 May 2015 3.708 12.36 12/10/2012 300000
    01 June 2015 14 November 2015 4.2 14 3/5/2017 1400000
    15 November 2015 31 May 2016 4.35 14.5 12/1/2017 200000
    01 June 2016 30 June 2017 4.5 15
    01 July 2017 18 October 2018 12 18
    ****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
    I want an If condition where Part A & Part B tax should be calculated based on payment date using > <, I have mentioned tax rates, request someone to help me. Thanks in Advance

User Tag List

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
  •  

 

 
DMCA.com