Excel - Replaced Nested IF with VLOOKUP - Episode 2030

Microsoft Excel Tutorial: Replace Nested IF with VLOOKUP in Excel | Simplify Your Commission, Bonus, and Discount Programs.

Microsoft Excel - Replace many nested IF with VLOOKUP.

Welcome to episode 2030 of the MrExcel podcast, where we will be discussing how to replace nested IF statements with VLOOKUP in Excel. Nested IF statements are commonly used for commission, discount, or bonus programs with different tiers. However, they can become long and complicated, and in older versions of Excel, you were limited to only 7 nested IF statements. Even with the new IFS function in Excel 2016, it can still be messy. That's why we're going to show you how to simplify this process using VLOOKUP.

First, we need to turn the rules of the program on their head. Instead of starting with the highest tier and working our way down, we will start with the lowest tier and work our way up. This will make it easier to create a VLOOKUP formula. We will also need to use the TRUE version of VLOOKUP, which is used for approximate matches. This will allow us to find the value just less than the one we are looking for.

One of the great things about using VLOOKUP is that it only requires one table, unlike nested IF statements which require multiple IF statements. However, if your manager doesn't want to see the lookup table, we have a solution for that too. We can embed the lookup table within the formula using a trick from Mike Girvin at ExcelIsFun. This will make it easier to edit the formula in the future, as you won't have to worry about the lookup table.

This is just one of the many tips we have in our book "40 Greatest Excel Tips of All Time". You can purchase the e-book for $10 or the print book for $25 by clicking the "i" on the top-right hand corner. In this episode, we are specifically addressing tiered commission, bonus, or discount programs. So if you're tired of dealing with long and complicated nested IF statements, give VLOOKUP a try. Just remember to turn the rules upside down and use the TRUE version of VLOOKUP for approximate matches. And if you don't want to see the lookup table, you can embed it within the formula using the Mike Girvin trick. Thanks for tuning in to another netcast from MrExcel!

Table of Contents:
(00:00) Replace Nested IF with VLOOKUP
(00:15) Common scenario with nested IF statements
(00:25) Importance of careful placement in nested IF statements
(00:40) Limitations of nested IF statements
(00:51) Introduction to using VLOOKUP instead
(01:21) Steps for using VLOOKUP in this scenario
(01:51) TRUE instead of FALSE in VLOOKUP
(02:26) Sorting the VLOOKUP table
(02:36) Embedding the lookup table in the formula
(03:30) Recap
(04:06) Buy the book
(04:16) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #excelvlookup #lookup #nestedif

This video answers these common search terms:
how to nest if formulas in excel
how to nest if in excel
can you nest if statements in excel
how to nest "if" in excel
how do i nest if statements in excel
how to nested if in excel
what is nested if function in excel
how to do nested if excel
how to do nested if statements in excel
how to do nested if then in excel
how to use nested if formula in excel
how to use nested if in excel
how to do a nested if function in excel
how to do a nested if statement in excel
how to write nested if function in excel
how to make a nested if function in excel
excel how to nest formulas

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.

[jasonm]

You say the limit is 32 nested if statements @0:59 (In notes as well)
Also mentioned on Podcast 2025 @2:49

Newer Excel versions have the limit at 64.
Page 114 of the book.

I remember because I changed over a massive 36 nested if statement formula using the tips from this chapter of the book.
This was one time where an array constant was perfect. Great tip.
Faster calculation than multiple if statements in my case as well.

[RubenQuijada]

Great tip. How I couldn't think about it

[daveblake]

If i had to "hide" the lookup table, I personally would not hard code it into the formula, I would hard code it directly to a named range, that way you have all of the benefits of a visible table making it easier to update if the rates change as you would only need to update the values in the named range and all the formulas would automatically update to reflect the new rates.

[carstenhansen]

Tip: Checking the sort of a VLOOKUP table. I found the following:

Write the formula =AND(B2:B7<=B3:B8) and press CTRL+Shift+Enter to make it an array formula.

It might be easier than making an extra column with b2<b3 and some count at total.

NB:
I do not know if above tip is already in the book.

[MikeDolanFliss]

Ooh. I've been using F9 for checking formulas since Mike G turned me on to it, but the embedded trick is clever...

Here's a crazy question - any way to extract that array from the formula back into some cells? I have no idea how. :p