With a tiered commission, bonus, or discount program, you often have to nest your IF functions
The Excel 2003 limit was 7 nested IF statements.
You can now nest 32, but I don't think you should ever nest 32
When would you ever use the approximate match version of VLOOKUP? This is the time.
Translate the discount program into a lookup table
VLOOKUP won't find the answer in most cases.
Putting ,True at the end will tell VLOOKUP to find the value just less.
This is the only time the VLOOKUP table has to be sorted.
Don't want the VLOOKUP table off to the side? Embed it in the formula.
F2 to edit the formula. Select the lookup table. Press F9. Enter.
The Excel 2003 limit was 7 nested IF statements.
You can now nest 32, but I don't think you should ever nest 32
When would you ever use the approximate match version of VLOOKUP? This is the time.
Translate the discount program into a lookup table
VLOOKUP won't find the answer in most cases.
Putting ,True at the end will tell VLOOKUP to find the value just less.
This is the only time the VLOOKUP table has to be sorted.
Don't want the VLOOKUP table off to the side? Embed it in the formula.
F2 to edit the formula. Select the lookup table. Press F9. Enter.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2030 - Replaced Nested IF with VLOOKUP!
I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Alright, this is really common with either a commission program, or a discount program, or a bonus program, where we have the tiers, different tiers, right?
If you're above $10000, you get 1% discount, $50000 5% discount.
You have to be careful when you build this nested IF statement, you start it at the top end if you're looking for greater than, or at the bottom end if you're looking for less than.
So B10>50000, 20%, otherwise another IF, B10>250000, 25%, and so on and so on.
This is just a long and complicated formula, and if your table’s larger, back in Excel 2003, you couldn't nest more than 7 IF statements, we're almost near the limit here.
You can go to 32 now, I don't think you should ever go to 32, and even if you're screaming “Hey wait, what about the new function that just came out in Excel 2016, the February 2016 release, with the IFS function?” Yeah sure, there's less parentheses here, and 87 characters instead of 97 characters, it's still a mess, let's get rid of this and do it with a VLOOKUP!
Alright, here are the steps, you take those rules, and you turn them on their head.
The first thing we have to say is, if you had $0 in sales, you get a 0% discount, if you had $10000 in sales, you get a 1% discount, if you have $50000 in sales, you get a 5% discount.
$100000, 10% discount, $250000, 15% discount, and then finally, anything >$500000 gets the 20% discount, alright.
Now lots of times, every time I'm talking about VLOOKUP, I say every VLOOKUP that you ever create is going to end in FALSE, and people will say “Well wait a second, what’s the TRUE version there for anyway?” It's for this very case where we're looking for a range, so we're looking up this value, a regular VLOOKUP, of course, ,2,FALSE will not find 550000, is going to return the #N/A!
So in this one very special case we’re going to change that FALSE to a TRUE, and that'll tell Excel “Go look for the 550000, if you can't find it, give us the value that's just less.” So it gives us the 20%, that’s what it does, alright?
And this is the only time your VLOOKUP table has to be sorted, all the other times with ,FALSE , it can be however you want.
And yes, you could leave the ,TRUE off, but I always put it there just to remind myself that this is one of those weird incredibly dangerous VLOOKUPs, and I don't want to copy this formula somewhere else.
Alright, so we'll copy and Paste Special Formulas, alright.
Next complaint: your manager doesn't want to see the lookup table, he wants it “Just get rid of that lookup table.” Alright, we can do that by embedding the lookup table, check this out, great trick that I got from Mike Girvin at ExcelIsFun.
F2 to put the formula into Edit mode, and then very carefully select just the range for the lookup table.
Press F9, and it takes this table and puts it in array nomenclature, and then I just press Enter like that.
Copy that down, Paste Special Formulas, and then I am free to get rid of the lookup table, that all just continues to work down the row.
This is a huge hassle if you have to come back in and edit this, you have to really stare at it with lots of clarity.
The comma means we're going to the next column, the semicolon means were going to the next row, alright, but you could in theory get back in there and change that formula if one of the chain numbers changes.
Alright, so using a VLOOKUP instead of a nested IF statement is tip #32 on our way to 40, “40 Greatest Excel Tips of All Time”, you can have this entire book.
Click that “i” on the top-right hand corner, $10 for an e-book, $25 for the print book, alright.
So today we're trying to solve a tiered commission bonus, or discount program.
Nested IFs are really common, watch out, 7 is all you can have in Excel 2003, today you can have 32, but you shouldn't ever have 32.
So when to use the approximate MATCH version of VLOOKUP, this is it.
Take that discount program, turn it upside down, make it into a lookup table starting with the smallest number, and going to the largest number.
VLOOKUP, of course, won't find the answer, but by changing the VLOOKUP to ,TRUE at the end, it'll tell it to find the value just less, this is the only time the table has to be sorted.
If you don't want to see that table out there, you can embed it in the formula using the Mike Girvin trick, F2 to edit the formula, select the lookup table, press F9, and then Enter.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Alright, this is really common with either a commission program, or a discount program, or a bonus program, where we have the tiers, different tiers, right?
If you're above $10000, you get 1% discount, $50000 5% discount.
You have to be careful when you build this nested IF statement, you start it at the top end if you're looking for greater than, or at the bottom end if you're looking for less than.
So B10>50000, 20%, otherwise another IF, B10>250000, 25%, and so on and so on.
This is just a long and complicated formula, and if your table’s larger, back in Excel 2003, you couldn't nest more than 7 IF statements, we're almost near the limit here.
You can go to 32 now, I don't think you should ever go to 32, and even if you're screaming “Hey wait, what about the new function that just came out in Excel 2016, the February 2016 release, with the IFS function?” Yeah sure, there's less parentheses here, and 87 characters instead of 97 characters, it's still a mess, let's get rid of this and do it with a VLOOKUP!
Alright, here are the steps, you take those rules, and you turn them on their head.
The first thing we have to say is, if you had $0 in sales, you get a 0% discount, if you had $10000 in sales, you get a 1% discount, if you have $50000 in sales, you get a 5% discount.
$100000, 10% discount, $250000, 15% discount, and then finally, anything >$500000 gets the 20% discount, alright.
Now lots of times, every time I'm talking about VLOOKUP, I say every VLOOKUP that you ever create is going to end in FALSE, and people will say “Well wait a second, what’s the TRUE version there for anyway?” It's for this very case where we're looking for a range, so we're looking up this value, a regular VLOOKUP, of course, ,2,FALSE will not find 550000, is going to return the #N/A!
So in this one very special case we’re going to change that FALSE to a TRUE, and that'll tell Excel “Go look for the 550000, if you can't find it, give us the value that's just less.” So it gives us the 20%, that’s what it does, alright?
And this is the only time your VLOOKUP table has to be sorted, all the other times with ,FALSE , it can be however you want.
And yes, you could leave the ,TRUE off, but I always put it there just to remind myself that this is one of those weird incredibly dangerous VLOOKUPs, and I don't want to copy this formula somewhere else.
Alright, so we'll copy and Paste Special Formulas, alright.
Next complaint: your manager doesn't want to see the lookup table, he wants it “Just get rid of that lookup table.” Alright, we can do that by embedding the lookup table, check this out, great trick that I got from Mike Girvin at ExcelIsFun.
F2 to put the formula into Edit mode, and then very carefully select just the range for the lookup table.
Press F9, and it takes this table and puts it in array nomenclature, and then I just press Enter like that.
Copy that down, Paste Special Formulas, and then I am free to get rid of the lookup table, that all just continues to work down the row.
This is a huge hassle if you have to come back in and edit this, you have to really stare at it with lots of clarity.
The comma means we're going to the next column, the semicolon means were going to the next row, alright, but you could in theory get back in there and change that formula if one of the chain numbers changes.
Alright, so using a VLOOKUP instead of a nested IF statement is tip #32 on our way to 40, “40 Greatest Excel Tips of All Time”, you can have this entire book.
Click that “i” on the top-right hand corner, $10 for an e-book, $25 for the print book, alright.
So today we're trying to solve a tiered commission bonus, or discount program.
Nested IFs are really common, watch out, 7 is all you can have in Excel 2003, today you can have 32, but you shouldn't ever have 32.
So when to use the approximate MATCH version of VLOOKUP, this is it.
Take that discount program, turn it upside down, make it into a lookup table starting with the smallest number, and going to the largest number.
VLOOKUP, of course, won't find the answer, but by changing the VLOOKUP to ,TRUE at the end, it'll tell it to find the value just less, this is the only time the table has to be sorted.
If you don't want to see that table out there, you can embed it in the formula using the Mike Girvin trick, F2 to edit the formula, select the lookup table, press F9, and then Enter.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
