Excel ? IF Statements, LOOKUPS & INDEX/MATCH. When to choose which one? – Part One.
- 1 Comment
This article will focus on the appropriate use of IF statements, and also show you when it’s better to move into more robust formulas like LOOKUP, VLOOKUP, HLOOKUP & INDEX/MATCH.
IF statements are one of the core formula models you can use in Excel, and they can be very powerful with regards to their logic. Very simply they follow this methodology: IF(something is True, then do something, otherwise do something else).
• =IF(A1=”Yes”,1,2), which simply says if A1 = Yes, then return a 1, otherwise return a 2.
You can also combine IF statements to evaluate multiple criteria by nesting them, however, many people try to get IF statements to do too much. Previous versions of Excel allowed up to 7 levels of nested IF statements, which means that you could evaluate up to 7 different criteria. Excel 2007+ allows 64 levels of nested IF statements, but just because you can do something doesn’t mean you should!
One of the most common uses of IF statements is to return data that matches specific criteria, or falls within a range of values. Here is a relatively common example of a nested IF statement to return letter grades based on test scores:
Another is calculating commission statements:
Note that in both formulas, the criteria need to be ordered sequentially in order for the formula to calculate correctly. So in the first example A1>=90 gets evaluated first, and if that condition is true, then the formula performs the calculation associated with that condition. If the condition isn’t true, then it moves onto the second, and so on. But if you get your conditions out of order, then one condition can invalidate the next and render your formula(s) useless. One of the inherent weaknesses with IF statements is that they need to be precise and ordered.
While both of these formulas work fine, they’re unwieldy and should be avoided if at all possible. Why? Primarily because the data in the formulas is static, so if the conditions driving the formula ever need to be changed, the formula needs to be manually adjusted as a result. Granted, the grades example isn’t too bad, because it’s not likely that information will change too often, but just imagine how much work you’d have to do if you have a lot of formulas like the commission example, and you have to change the criteria. Ideally, you’ll get into the habit of only using IF statements for Text comparisons like the earlier example. Yes/No/Maybe or Male/Female evaluations are very common, and the nice thing about them is that criteria aren’t likely to change very often. If you find yourself with situations like this then by all means use IF statements, otherwise it’s time to move up to more robust alternatives, starting with LOOKUP.
One of the primary reasons to move away from IF statements for multiple criteria is so that you can use “table based” reference data. This gives you the ability to have your data points on a worksheet, where the values can be easily changed, as opposed to hardcoded in a formula, where changing the values can be a challenge. Many workbooks have broken because of numerous IF statements that someone didn’t update. In the commission example above, what would happen if you needed to change the 2% and you had hundreds of formulas depending on that one? It wouldn’t be fun, and that’s a relatively small example. Imagine one with 64 conditions! And that’s if you can even find the formula in the first place! Table based dependencies are much easier to change on the fly, which can mean a lot especially if you’re dealing with complex models and testing multiple criteria.
Note: data tables that drive referential formulas don’t need to be housed in the open where users can change them. Very often you’ll find sensitive tables housed on hidden worksheets, where only the creator/administrator can change the data.
The first formula in the family of referential formulas is LOOKUP (they’re called referential formulas because they can return a reference to a value, or a value in a range of values). LOOKUP has plenty of uses, but it’s also the most fragile and least flexible in the Lookup family. And that will be the subject of part two of the posts, how to use the Microsoft Excel VLookup.
Here are a few quality sources on the subject. Each of these sites offers free quality education pertaining to Microsoft Excel. One is a Microsoft help forum, one is Microsoft’s site itself, and the third is a site owned and operated by one of the most respected professionals in the business.
- Do you prefer to learn via free Microsoft Excel training videos? If so, here is the link where you can see not only Excel Vlookup, Index, Match, etc., training at the expert level.
- Do you want to see what Microsoft has to say about Index/Match? If so, click this link.
- Or do you prefer to learn how to use the Index/Match functions by Debra of Contextures.Com, one of the top sites on Microsoft Excel in the world, and one of the Microsoft Excel MVPs in Canada. If so, click here to see what Debra says about Microsoft Excel’s Index Match.
We originally wrote this post a few years back. I uncovered it the other day and I thought it still had value to the visitor’s of this site. It is too long for one post, so we will break it up into a few, or you can click this link to read the full article now.
NOTES: this article is written for Excel 2010 and its Ribbon Interface. The examples you see were created in Excel 2010, but saved as Excel 97?2003. Working examples are hosted on Microsoft’s [url=http://cid?8cffdec0ce27e813.skydrive.live.com/redir.aspx?resid=8CFFDEC0CE27E813!195]SkyDrive service[/url] for you to download
Stay tuned for part two of this article, where we go over the Microsoft Excel VLookp in detail. That then followed by the use of the Index/Match Functions.