Logic with IF, AND, OR Functions in Excel

IF statement with AND/OR. You'll use an IF statement with either an AND function or an OR function to expand the functionality of the IF function by adding an AND or an OR criterion into the logical test. I'm going to do a quick review of the AND/OR statement before we take a look at how you can combine that with the IF statement.

So in this situation, I'm going to pretend there's a dealership that is going to give loan approvals to people who have at least $99 and a job. If we write that as an AND statement, it'll look like this: equal AND(this person needs to have at least $99, so that amount needs to be greater than or equal to 99. When I enter the comma, I can actually use the word "and" their employment status needs to be employed, so they need to have a job.

So J9 needs to be equal to "JOB." That's how we're determining job status. Close parentheses, press ENTER, and that person is approved for the loan.

To save time writing the OR statement, I'll simply AutoFill down and go into the function and change the AND function to an OR function. Now I don't have to write as much. I'll press ENTER, and there we go. Now the way this works for an AND statement, if someone doesn't have at least $99, their loan approval is FALSE.

Let's say they have $99, but the problem is they don't have a job. They are also not approved. Now when you get to the OR statement, only one of the criteria needs to be true.

If someone has $50, they still are approved for the loan, and if their problem is that they don't have a job, it's actually not a problem. They still get the loan approval. The only way the OR statement will result in FALSE is if all the logical tests are false.

All right, so now let's see how we can use an IF statement with this. So to use an IF statement, I'm going to do this in a simple way. I want to be able to say someone is approved if they've met the criteria.

I can't do that with the AND/OR statement because as you can see, I only have TRUE or FALSE as the result of the AND/OR statement. So I'm going to type equal IF, and I'm going to check their loan approval status. If it is true, then I'm going to say that that person was approved, and if they were not, I'm going to say not approved.

I'll keep it really simple, then I'll press ENTER, and there we go. I'll AutoFill this down. I don't have to change anything because I'm referencing the right value.

So how does this work if I change this to $50? The loan approval status is triggering the right response. I do want FALSE to be equal to "Not Approved," and I do want TRUE to be equal to "Approved." So I'm using this individual cell as an intermediate step because what's really doing the work is the AND/OR statement.

So to combine the IF statement with the AND/OR statement, what I should do is go to this cell and replace K9 with the engine that's making the AND/OR statement work and giving me a TRUE or FALSE in this cell. So I'll go back to the IF statement and replace K9 with an AND/OR statement. That's how you would use the AND/OR statement with an IF statement to get you the result.

Now I don't need to depend on K9. I could actually remove that value, but this may be a little daunting if you're writing a formula like this, so this is why I showed you separately how to write the AND/OR statement and then use it within the IF statement. I'll press ENTER, and the same results.

If I go here and change this to $50, it goes to FALSE and not approved. If I go over and change it to 99, it updates. So let's take a look at an example in this exercise here.

We're looking to check the legal status of these three people. We want to see if they're legal drivers. In a prior exercise, TRUE would mean that they were legal and FALSE would mean they're not legal.

Now we're able to actually use the words "Legal" or "Not Legal." Now to start this out, maybe we'll just start out by writing the AND statement. In this case, it's an AND statement that we're working with because you're legal if you're 17 or older and pass the road test.

So this is going to be equal to AND(this value needs to be greater than or equal to 17, and that value needs to be equal to "Yes"). Close parentheses, and I'll press ENTER. TRUE.

So I get TRUE all around for this first person. I'll AutoFill down, and I get TRUE and FALSE. Now I want to change TRUEs and FALSEs to "Legal" and "Not Legal."

Now I'll type an IF statement at the very beginning, right after the equal sign, open parentheses, and I'm basically saying the logical test is the AND statement. Now if that whole AND statement results in TRUE, then I'll enter a comma and write what I want to display in the cell. In this case, "Legal."

Then I'll enter a comma and then write what I want to display if it's FALSE, and that is "Not Legal." Close parentheses, and I'll press ENTER. So TRUE became "Legal," and now we have TRUE and FALSE.

If I AutoFill this down, we'll get the appropriate status for these drivers. So that's how you would use the AND/OR statement with an IF statement. Again, exercises like this can be complicated, so if you click the plus sign in the appropriate row, you'll be able to see our answer in the spreadsheet, and you can verify whether or not what you did matches our answer. We have that for the rest of the exercises in this sheet, including the ones I haven't done.

So that is the IF statement with the AND/OR.