Share via

Stringing Multiple IF functions together.

Anonymous
2023-12-02T17:43:17+00:00

I am working on a large data set with multiple columns I want to reference to determine what a "Deficiency" would be. I am using IF functions and the first "part" of it works. I want to add another, similar condition and it will allow it without errors but comes back as "TRUE" or "FALSE" only. I actually have one more part I would like to add to this formula, but can't figure out this second step.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2023-12-02T19:29:06+00:00

    Hi Jordan. I am an Excel user like you.

    How to add an imbedded IF function inside another IF function depends on what kind of conditions you have and what result you want. Here are two basic illustrations of how to put them together.

    1. =IF(A1="RED","Christmas",IF(A1="Green","Easter","None"))

    In this formula the first Logical Test is A1="Red". The true result is "Christmas". So, if A1 does have "Red" the result will be "Christmas". The imbedded IF function is in the Value if False position. If A1 does not have "Red" the second IF function comes into play as the Value if False option and if A1 has "Green" then the result will be "Easter" as the true result of the second IF function. If A1 does not have "Green" then the result will be "None" as the False result of the second IF function.

    1. =IF(A1="Red",IF(B1="Green","Christmas","Valentine's"),"None")

    In this formula the second IF function is in the Value if True position so if A1 has "Red" it will go on to the Value if True IF function to check if B1 has "Green". If this is true (we have met both Logical Tests now) then the result will be the true result of the second IF function "Christmas". If the Logical Test is false and B1 doesn't have "Green", then we have met the first test but not the second so the result will be the false result of the second IF function "Valentine's". If on the other hand the first Logical Test is false and A1 does not have "Red" then the formula will jump over the Value if True IF function and go to the Value if False part of the first IF function that is after the second, completed, imbedded IF function and the result will be "None".

    Hopefully this will help on how these are constructed. If you need more specific information you will need to include some basic details about your data and layout. Be sure to remove sensitive information.

    3 people found this answer helpful.
    0 comments
  2. Rich~M 20,370 Reputation points Volunteer Moderator
    2023-12-04T00:23:54+00:00

    Hi Jordan.

    You could check out the SWITCH function that peiyezhu referred to, or I believe that the IFS function may be more applicable to your data. If I read your data correctly, you have unique conditions for each of the five results you want. With the IFS function you can enter each of those conditions followed by the result you want for that particular set of conditions.

    Here is an example of how the formula works.

    =IFNA(IFS(AND(A1=1,A2=2),"Blue",AND(A1=1,A2=3),"Green",AND(A1=1,A2=4),"Red"),"")

    Image

    .

    Inside the IFS function I have included three AND functions as the condition statements and three results, one for each of the condition statements. The first A1=1 and A2=2 is false so there is no result. The second A1=1 and A2=3 is true so the result that is matched with that statement is Green. This works best if each of the conditions is mutually exclusive from the others, but you can have them set in an order because Excel will stop at the first true result.

    You can enter your AND, OR, or AND/OR statements in the Logical Test positions with the appropriate result following each. I also wrapped this in an IFNA function because if none of the conditions is found to be true the result will be #N/A. By using the IFNA function you can control what the result will be in that case. The formula is set to return a blank cell with the two quotation marks at the end of the formula. If you want it to say something else just enter that value inside those quotation marks at the end of the formula.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2023-12-04T17:26:32+00:00

    Rich,

    Thank you very much! I will give this a shot. I had tried using IFS before, but, after seeing your example, I'm pretty sure I had the formatting wrong. Thank you, I'll update shortly!

    0 comments
  5. Anonymous
    2023-12-03T21:43:14+00:00

    Hey Rich, thanks very much for your response. The basic formatting of nesting them, with a singular criteria is something I've managed but in this particular case, I'm using AND and OR functions along with it. I thought I posted a picture in my original post, but I'll try again here. If it comes up, you can see i have a successful formula for one condition, but I want to add four other conditions for "Positive Elevation", only "Lateral, "Negative Elevation", and "Lateral/Positive Elevation".

    0 comments