You wrote some IF functions to assign a score based on numerical ranges. You want to combine those formulas into a single long Excel formula. This video will show you how to do it and how to avoid some pitfalls.
Along the way, you will see IF, AND, IFS, LET, CHOOSE, INT, VLOOKUP, LOOKUP, XLOOKUP, and XMATCH.
There are really two videos here. The first video shows how to combine 120 characters of IF formulas down to 49 characters. After 5:40 in the video, this turns into an advanced video on how to shorten the formula.
Table of Contents
(0:00) Two videos today from one question
(0:23) How to assign scores to ranges in Excel
(0:42) Solving with an IF formula for each range
(0:58) Using AND with IF in Excel
(2:18) Liam Bastick's Rule of Thumb for Formula Length
(3:21) Combining Five Excel IF formulas into one
(4:03) You don't have to test again for previous ranges in IF
(5:25) Ace your job interview on Retrieve
(5:42) Deep dive how to shorten a formula in Excel
(6:06) Using IFS or LET
(7:35) Using clever Math instead of IF
(8:44) Replace many IFS with VLOOKUP in Excel
(10:07) Replace many IFS with MATCH in Excel
(11:00) Shorter Excel formula with Named Ranges
(11:54) Doing all lookups in one formula
(13:15) Advanced Excel on Retrieve
(13:32) Wrap-up
Along the way, you will see IF, AND, IFS, LET, CHOOSE, INT, VLOOKUP, LOOKUP, XLOOKUP, and XMATCH.
There are really two videos here. The first video shows how to combine 120 characters of IF formulas down to 49 characters. After 5:40 in the video, this turns into an advanced video on how to shorten the formula.
Table of Contents
(0:00) Two videos today from one question
(0:23) How to assign scores to ranges in Excel
(0:42) Solving with an IF formula for each range
(0:58) Using AND with IF in Excel
(2:18) Liam Bastick's Rule of Thumb for Formula Length
(3:21) Combining Five Excel IF formulas into one
(4:03) You don't have to test again for previous ranges in IF
(5:25) Ace your job interview on Retrieve
(5:42) Deep dive how to shorten a formula in Excel
(6:06) Using IFS or LET
(7:35) Using clever Math instead of IF
(8:44) Replace many IFS with VLOOKUP in Excel
(10:07) Replace many IFS with MATCH in Excel
(11:00) Shorter Excel formula with Named Ranges
(11:54) Doing all lookups in one formula
(13:15) Advanced Excel on Retrieve
(13:32) Wrap-up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2465. How to combine multiple IF formulas.
And then episode 2465 ½. Excel Lambda and I just shortened a formula by 99.8%.
Can you beat that?. And I'm sure some of you can.
12,000 characters in formula down to 16. Alright, so here's our question.
Our manager comes to us and says, hey look, we have number of units sold over here.
Give everyone a score based on this. Fifteen to 29, you get a 1.
Thirty to 59, you get a 2. Greater than 60, you get a 3.
If you didn't get 15, then 0. Write a formula that's going to do this.
Here is an approach to this.
The first formula here in column CC is fairly simple.
We're just looking over there in CB3 if it's less than 15 then you get 0. Otherwise quote quote an empty cell.
And then here. Now this is the tricky one, right?
Because we have two tests. You have to be greater than or equal to 15.
And less than 30.
So in order to put two tests inside the IF statement, we have to wrap that in AND function. Now in this case.
There's only two arguments, but you can have up to 254 arguments and all of them have to be True in order for and to be True.
Right, so this is where we are at: 15 to 29, you get a 1. Otherwise nothing.
Test three is very, very similar.
If we're greater than or equal to 30 and less than 60, you get a 2. And then Test 4 over here.
If you're greater than or equal to 60 you get a 3, otherwise nothing.
And then to put it all together, relatively new from February of 2017, the CONCAT formula will put all those together.
And of course, since only one of those can be true, you're going to get either zero, one, two, or three.
Not bad.
In fact, if you're the person who created this good for you, right? This is some fairly advanced excel going on here.
Using AND. A lot of people don't know how to use AND.
I mean you're you're above 80% of the Excel people at this point.
And my term for this is a term that's very familiar in Britain and Australia.
It's called I'm chuffed to bits. That's not a term rate used in America.
I had to go look it up, but good for you if you wrote this.
That is a really good formula. But, we are going to make it better.
I'm going to geek out here.
A friend of mine in Australia, Liam Bastick, has written several books: Introduction to Financial Modeling and then Continuing Financial Modeling.
And they both have this great rule of thumb right? And I'm stealing this from from Liam.
And he says he's stealing from a colleague of his.
The idea: the formula in your Formula Bar should be no longer than your thumb.
And right now we have a formula that's longer than that.
You know when I also go back to when I was a judge at the ModelOff This is back in 2012, 2013?
It was in New York City. They had the main competition.
But then they also had this side bet.
And it was a $5000 prize for solving a puzzle with the shortest formula. So you know what's best?
We're looking for the shortest length. But also my rule: don't be a jerk, right?
Don't come with the shortest formula if no one will be able to understand it.
And I'm going to admit that in this video I'm going to have an unhealthy obsession with trying to get the shortest formula. Just you'll be warned in advance.
OK, so you might remember this from episode 2464.
This kind of picks up exactly at the point where we are right now. The person who wrote these formulas.
It's all working.
Good for you: trying to shorten it down into a single formula. And I'm not sure where they went wrong.
Adding this extra CB3 in here and here. And that didn't work.
How to shorten this down?
If CB3 is less than 15 and then after that next IF statement, in the AND function, you don't have to repeat CB3 again.
And we come up with this 90 character formula. And that's impressive.
Like using the rule of thumb, that's a 25% reduction in size.
Went from 120 characters of formula up here, down to 90.
That's really good, but I think there's still one massive improvement that we can do.
If you look at this. When it was separate columns.
It made sense for you to have to do the test to make sure that we're greater than or equal to 15 and less than 30. But that's not necessary now.
Once you've know that it's not less than 15, you don't ever have to check for that again in the same formula.
If it had been less than 15, we would have gotten a 0 here.
Here, in this 90 character formula, when we go check to see “hey, is this greater than or equal to 15?” We already know it is.
Because we wouldn't have gotten to this part of the formula, had this been true.
In order to do what I'm suggesting here, you have to make sure that you start from the smallest and work your way up to the largest.
But we can very easily take out all of these red characters here.
And we get shortened down to 77.
And then, out here, we don't have to test to see if it's greater than or equal to 30. We already know that it's greater than 30.
Because if it wasn't, it would have gotten either the zero or the one.
So we wouldn't have made it to this point in the formula.
And then finally, we don't have to check to see if it's greater than 60.
If we get here.
If we get to the Value_If_False, it has to be greater than 60.
Otherwise it would have gotten one of these, right?
So that is an impressive reduction in size from 120 characters down to 49. Fifty-Nine Point 2 percent reduction in size.
And for those of you who are here to try and figure out how to do multiple ifs in a formula, this is where you should stop. That's the form that I recommend that you use.
Very fast, very efficient.
I have a course that you'll love out in the Retrieve platform.
“Ace your job interview where you have to know Excel”.
For those of you who are here to figure out how to combine multiple IFS, thank you for stopping by. Stop back for another netcast from MrExcel.
But for my regular viewers, I can already hear you saying, oh wait, we can do better than this.
Do they really need these in quotes? The zero, the one, the two, the three.
We can save 8 characters right away by putting the number 0, 1, 2 and three.
It's probably more likely to be useful down the road to have those be real numbers instead of text numbers. So now we're down to 65.8% reduction.
And we can get even shorter by using a new function that came along in February 2017.
Hey, you must have it because you were using CONCAT earlier.
The plural version of IFS. This is cool.
You don't have to nest multiple functions inside of each other.
If CB3, that's the first test, if that's less than 15, then zero.
And then the next test. If it's less than 30.
Now again we wouldn't have gotten here if the first one wasn't true, then 1.
If CB3 is less than 60, then a two. Hey look, this is my unhealthy obsession.
Technically, that “1” right there is supposed to say True.
But that would cost me 4 characters to put a True there.
And uh, 1 is the same as a True.
So like this next-to-the-last argument is always a true and then what to do if the other stuff isn't true.
So if we're greater than or equal to 60, then we get a 3 right?
And now I'm down to 36 characters.
And then it was XL-Lambda in comment on yesterday's video that suggested this LET, right.
Lots of advantages to LET because we get to redefine CB3 as the variable A right? So that should save us 2 characters.
There are two characters. There are two characters there.
Unfortunately the defining CB3 that costs a few characters and it actually ends up being longer.
Not casting any shade to XL-Lambda.
It was an idea from Excel Lambda that's going to save us dramatic formula length coming up. You know where I said we should have stopped?
I realize at this point that I am going insane. But you know, you have to ask the question.
Is there some clever math that we can just get rid of the whole IF statement and solve the problem? So take this score divided by 15.
Send it into the INT function, the integer function.
Boy, that works for 0, 1, 2. But right here at 45.
Because the manager was clever, kind of using something like a logarithmic scale here, not a true logarithmic scale, but it's harder to get to a 3 than it was to get to a 2.
This is not going to work, I'm going to get 0, 1, 2 3, 4 when I needed 0, 1, 2, 3.
That would have been Golden. Twelve character formula – Whoo-hoo!
That doesn't work. Because the manager.
But I could take this formula, which is going to give me the number 0 through 4.
Add one to it. Send it into the CHOOSE function.
And then change that to 0,1,2,2,3.
While this seems like a silly formula, it still is better than the IFS. The IFS was 36 and this is 32.
Your coworkers will hate this. No one will be able to understand this.
It violates my “the person has to be able to understand what's going on” rule.
I know so many people are going to hate VLOOKUP.
The person who sent this question in, probably is not at the VLOOKUP stage.
And everyone who's still watching at this point thinks I should be using INDEX and MATCH instead of VLOOKUP.
But you have to admit the “comma True” version of VLOOKUP which is great.
You don't need the “comma True” at the end or the “comma 1” at the end.
It is the default. Which, most of the time I grumble about.
So we just built our little table here. Zero gets you zero points.
Fifteen or higher gets you one point. Thirty or higher gets you 2.
Sixty or higher, gets you 3. Simple little VLOOKUP there.
Down to 25 characters from 32. That's a 79% reduction.
Probably actually fairly easy to understand.
Except you have to have a look-up table somewhere on the sheet. Joe McDaid gave us XLOOKUP.
It's far more robust. It's better in every way.
Except for this particular test. Because I have to put the C2 to C5 range in there.
And then the D2 to D5. With all the dollar signs.
It ends up being a lot longer And it's funny, we have to go back to Dan Bricklin and Bob Frankston and their LOOKUP function from VisiCalc.
It was a crazy function. You're looking up the 30.
It's always “comma true” by default.
And what you're returning is the last column of the lookup range. So it is an amazingly short formula there.
22 and 81.7% reduction. Let's just check for the INDEX and MATCH folks.
So we were 22. The MATCH gets us down to 25.
It's nice the MATCH only needs a one column table with where are the breaks.
That one there is actually optional because in the MATCH that's the default, so we can get down to 23, right? That's pretty good.
It definitely beats VLOOKUP, but it doesn't beat LOOKUP.
And again XMATCH. It defaults to an exact match.
So we have to put the negative one in there. In both cases it's returning an offset.
We're not getting a 0 where we need it.
So unfortunately, none of the MATCH or XMATCH can beat LOOKUP.
I did try and see what would happen if we embedded the table right.
So let's just get rid of the table and we're still at 26.
Instead of 22, so it's not the best formula. And then let's cheat a little bit.
Let's name these ranges. Here's the first look up table.
Here's the other look table. Let's name them right.
So now a match with CB3 and then using the name of “V”.
That gets us down to 15. Whoo-hoo!
That's the shortest, But then LOOKUP, look up with this range being called W. That gets us to 14.
And that's an 88.3% reduction in size from the original 120.
If we're really trying to win $5000 at the ModelOff competition back in 2013.
This, I think, is the formula that I would come down to.
Now I know some of you watching this are going to have something shorter.
You're going to have some crazy thing that I've never thought of.
And by all means, please, put it down in the YouTube comments.
But it was XL-Lambda who gave me this idea yesterday.
It's not just the 14 characters.
It's not these 14 characters because, you know, we probably have 100 employees.
And we have to copy this formula 100 times so it's not 14 characters, it's 1400 characters.
Right, so rather than have this and this and this and this and this and this and this on every row. Let's do all of those Lookups at once, right?
So here this this awesome formula. Equal LOOKUP of the numbers.
Comma W, so we're using the named range. And that formula is a little bit longer.
It's 16 instead of 14.
But it's one formula that will return all of the results.
100 Results, 1000 results, whatever you throw at it.
Really, when I said we were down to 14, we weren't down to 14. We were down to 1400.
Assuming 100 rows of data.
And now we just went from 1400 to 16, which is an astounding 99.867%. Now listen to me.
Right? In that second title card.
I'm claiming that XL-Lambda and I came up with this reduction.
Let's be completely fair. I got us to 88.33.
XL-Lambda got us the rest of the way. So good for you, XL-Lambda.
If you have something better than this and I'm sure you do, please by all means down below let me know.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video, and there's a complete transcript in several languages.
It's a super fast way to learn.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below. Well, there you are two videos all in one video.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let us hear you Nancy.
And then episode 2465 ½. Excel Lambda and I just shortened a formula by 99.8%.
Can you beat that?. And I'm sure some of you can.
12,000 characters in formula down to 16. Alright, so here's our question.
Our manager comes to us and says, hey look, we have number of units sold over here.
Give everyone a score based on this. Fifteen to 29, you get a 1.
Thirty to 59, you get a 2. Greater than 60, you get a 3.
If you didn't get 15, then 0. Write a formula that's going to do this.
Here is an approach to this.
The first formula here in column CC is fairly simple.
We're just looking over there in CB3 if it's less than 15 then you get 0. Otherwise quote quote an empty cell.
And then here. Now this is the tricky one, right?
Because we have two tests. You have to be greater than or equal to 15.
And less than 30.
So in order to put two tests inside the IF statement, we have to wrap that in AND function. Now in this case.
There's only two arguments, but you can have up to 254 arguments and all of them have to be True in order for and to be True.
Right, so this is where we are at: 15 to 29, you get a 1. Otherwise nothing.
Test three is very, very similar.
If we're greater than or equal to 30 and less than 60, you get a 2. And then Test 4 over here.
If you're greater than or equal to 60 you get a 3, otherwise nothing.
And then to put it all together, relatively new from February of 2017, the CONCAT formula will put all those together.
And of course, since only one of those can be true, you're going to get either zero, one, two, or three.
Not bad.
In fact, if you're the person who created this good for you, right? This is some fairly advanced excel going on here.
Using AND. A lot of people don't know how to use AND.
I mean you're you're above 80% of the Excel people at this point.
And my term for this is a term that's very familiar in Britain and Australia.
It's called I'm chuffed to bits. That's not a term rate used in America.
I had to go look it up, but good for you if you wrote this.
That is a really good formula. But, we are going to make it better.
I'm going to geek out here.
A friend of mine in Australia, Liam Bastick, has written several books: Introduction to Financial Modeling and then Continuing Financial Modeling.
And they both have this great rule of thumb right? And I'm stealing this from from Liam.
And he says he's stealing from a colleague of his.
The idea: the formula in your Formula Bar should be no longer than your thumb.
And right now we have a formula that's longer than that.
You know when I also go back to when I was a judge at the ModelOff This is back in 2012, 2013?
It was in New York City. They had the main competition.
But then they also had this side bet.
And it was a $5000 prize for solving a puzzle with the shortest formula. So you know what's best?
We're looking for the shortest length. But also my rule: don't be a jerk, right?
Don't come with the shortest formula if no one will be able to understand it.
And I'm going to admit that in this video I'm going to have an unhealthy obsession with trying to get the shortest formula. Just you'll be warned in advance.
OK, so you might remember this from episode 2464.
This kind of picks up exactly at the point where we are right now. The person who wrote these formulas.
It's all working.
Good for you: trying to shorten it down into a single formula. And I'm not sure where they went wrong.
Adding this extra CB3 in here and here. And that didn't work.
How to shorten this down?
If CB3 is less than 15 and then after that next IF statement, in the AND function, you don't have to repeat CB3 again.
And we come up with this 90 character formula. And that's impressive.
Like using the rule of thumb, that's a 25% reduction in size.
Went from 120 characters of formula up here, down to 90.
That's really good, but I think there's still one massive improvement that we can do.
If you look at this. When it was separate columns.
It made sense for you to have to do the test to make sure that we're greater than or equal to 15 and less than 30. But that's not necessary now.
Once you've know that it's not less than 15, you don't ever have to check for that again in the same formula.
If it had been less than 15, we would have gotten a 0 here.
Here, in this 90 character formula, when we go check to see “hey, is this greater than or equal to 15?” We already know it is.
Because we wouldn't have gotten to this part of the formula, had this been true.
In order to do what I'm suggesting here, you have to make sure that you start from the smallest and work your way up to the largest.
But we can very easily take out all of these red characters here.
And we get shortened down to 77.
And then, out here, we don't have to test to see if it's greater than or equal to 30. We already know that it's greater than 30.
Because if it wasn't, it would have gotten either the zero or the one.
So we wouldn't have made it to this point in the formula.
And then finally, we don't have to check to see if it's greater than 60.
If we get here.
If we get to the Value_If_False, it has to be greater than 60.
Otherwise it would have gotten one of these, right?
So that is an impressive reduction in size from 120 characters down to 49. Fifty-Nine Point 2 percent reduction in size.
And for those of you who are here to try and figure out how to do multiple ifs in a formula, this is where you should stop. That's the form that I recommend that you use.
Very fast, very efficient.
I have a course that you'll love out in the Retrieve platform.
“Ace your job interview where you have to know Excel”.
For those of you who are here to figure out how to combine multiple IFS, thank you for stopping by. Stop back for another netcast from MrExcel.
But for my regular viewers, I can already hear you saying, oh wait, we can do better than this.
Do they really need these in quotes? The zero, the one, the two, the three.
We can save 8 characters right away by putting the number 0, 1, 2 and three.
It's probably more likely to be useful down the road to have those be real numbers instead of text numbers. So now we're down to 65.8% reduction.
And we can get even shorter by using a new function that came along in February 2017.
Hey, you must have it because you were using CONCAT earlier.
The plural version of IFS. This is cool.
You don't have to nest multiple functions inside of each other.
If CB3, that's the first test, if that's less than 15, then zero.
And then the next test. If it's less than 30.
Now again we wouldn't have gotten here if the first one wasn't true, then 1.
If CB3 is less than 60, then a two. Hey look, this is my unhealthy obsession.
Technically, that “1” right there is supposed to say True.
But that would cost me 4 characters to put a True there.
And uh, 1 is the same as a True.
So like this next-to-the-last argument is always a true and then what to do if the other stuff isn't true.
So if we're greater than or equal to 60, then we get a 3 right?
And now I'm down to 36 characters.
And then it was XL-Lambda in comment on yesterday's video that suggested this LET, right.
Lots of advantages to LET because we get to redefine CB3 as the variable A right? So that should save us 2 characters.
There are two characters. There are two characters there.
Unfortunately the defining CB3 that costs a few characters and it actually ends up being longer.
Not casting any shade to XL-Lambda.
It was an idea from Excel Lambda that's going to save us dramatic formula length coming up. You know where I said we should have stopped?
I realize at this point that I am going insane. But you know, you have to ask the question.
Is there some clever math that we can just get rid of the whole IF statement and solve the problem? So take this score divided by 15.
Send it into the INT function, the integer function.
Boy, that works for 0, 1, 2. But right here at 45.
Because the manager was clever, kind of using something like a logarithmic scale here, not a true logarithmic scale, but it's harder to get to a 3 than it was to get to a 2.
This is not going to work, I'm going to get 0, 1, 2 3, 4 when I needed 0, 1, 2, 3.
That would have been Golden. Twelve character formula – Whoo-hoo!
That doesn't work. Because the manager.
But I could take this formula, which is going to give me the number 0 through 4.
Add one to it. Send it into the CHOOSE function.
And then change that to 0,1,2,2,3.
While this seems like a silly formula, it still is better than the IFS. The IFS was 36 and this is 32.
Your coworkers will hate this. No one will be able to understand this.
It violates my “the person has to be able to understand what's going on” rule.
I know so many people are going to hate VLOOKUP.
The person who sent this question in, probably is not at the VLOOKUP stage.
And everyone who's still watching at this point thinks I should be using INDEX and MATCH instead of VLOOKUP.
But you have to admit the “comma True” version of VLOOKUP which is great.
You don't need the “comma True” at the end or the “comma 1” at the end.
It is the default. Which, most of the time I grumble about.
So we just built our little table here. Zero gets you zero points.
Fifteen or higher gets you one point. Thirty or higher gets you 2.
Sixty or higher, gets you 3. Simple little VLOOKUP there.
Down to 25 characters from 32. That's a 79% reduction.
Probably actually fairly easy to understand.
Except you have to have a look-up table somewhere on the sheet. Joe McDaid gave us XLOOKUP.
It's far more robust. It's better in every way.
Except for this particular test. Because I have to put the C2 to C5 range in there.
And then the D2 to D5. With all the dollar signs.
It ends up being a lot longer And it's funny, we have to go back to Dan Bricklin and Bob Frankston and their LOOKUP function from VisiCalc.
It was a crazy function. You're looking up the 30.
It's always “comma true” by default.
And what you're returning is the last column of the lookup range. So it is an amazingly short formula there.
22 and 81.7% reduction. Let's just check for the INDEX and MATCH folks.
So we were 22. The MATCH gets us down to 25.
It's nice the MATCH only needs a one column table with where are the breaks.
That one there is actually optional because in the MATCH that's the default, so we can get down to 23, right? That's pretty good.
It definitely beats VLOOKUP, but it doesn't beat LOOKUP.
And again XMATCH. It defaults to an exact match.
So we have to put the negative one in there. In both cases it's returning an offset.
We're not getting a 0 where we need it.
So unfortunately, none of the MATCH or XMATCH can beat LOOKUP.
I did try and see what would happen if we embedded the table right.
So let's just get rid of the table and we're still at 26.
Instead of 22, so it's not the best formula. And then let's cheat a little bit.
Let's name these ranges. Here's the first look up table.
Here's the other look table. Let's name them right.
So now a match with CB3 and then using the name of “V”.
That gets us down to 15. Whoo-hoo!
That's the shortest, But then LOOKUP, look up with this range being called W. That gets us to 14.
And that's an 88.3% reduction in size from the original 120.
If we're really trying to win $5000 at the ModelOff competition back in 2013.
This, I think, is the formula that I would come down to.
Now I know some of you watching this are going to have something shorter.
You're going to have some crazy thing that I've never thought of.
And by all means, please, put it down in the YouTube comments.
But it was XL-Lambda who gave me this idea yesterday.
It's not just the 14 characters.
It's not these 14 characters because, you know, we probably have 100 employees.
And we have to copy this formula 100 times so it's not 14 characters, it's 1400 characters.
Right, so rather than have this and this and this and this and this and this and this on every row. Let's do all of those Lookups at once, right?
So here this this awesome formula. Equal LOOKUP of the numbers.
Comma W, so we're using the named range. And that formula is a little bit longer.
It's 16 instead of 14.
But it's one formula that will return all of the results.
100 Results, 1000 results, whatever you throw at it.
Really, when I said we were down to 14, we weren't down to 14. We were down to 1400.
Assuming 100 rows of data.
And now we just went from 1400 to 16, which is an astounding 99.867%. Now listen to me.
Right? In that second title card.
I'm claiming that XL-Lambda and I came up with this reduction.
Let's be completely fair. I got us to 88.33.
XL-Lambda got us the rest of the way. So good for you, XL-Lambda.
If you have something better than this and I'm sure you do, please by all means down below let me know.
Now, if you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video, and there's a complete transcript in several languages.
It's a super fast way to learn.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below. Well, there you are two videos all in one video.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let us hear you Nancy.
