- Roll On Through: Create an Iterative List in Excel Without Any Real Coding
- Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel
- Active-X Gonna Give it to Ya
- Your Invitation to the Excel Pivot Party
- Slicing Excel: The Way of The Knife
- Yes...If(And)): Excel’s Mighty “IF” Statement
- Getting Iffy With it: Conditional Statements in Excel
- Taking Out the Trash: Dirty Data in Excel
- Dealing with Dirty Data in Excel
- Doing the Excel Data Dance
- Adventures in Excel: Power to the Pivot
- The Art of Excel Pivot Tables
- The One Excel Formula to Rule Them All
- Excel Showdown: Index Match vs. "The Lookups"
- How I Stopped Worrying and Learned to Love the Formula
- Excel Data Manipulation: a Guide For Everyone
You may have picked up in my last post that some fellow wizards (of which I now consider you if you've mastered Index-Match, and you've activated developer mode) who may have started learning Excel prior to 2007 may drop the phrase "lookup;" generally in the context of "V-Lookup" (or perhaps "H-Lookup" if they're working with data that was formatted by savages).
The question is, if people "in the know" are always talking about and using these two formulas, then what do they do, and why haven't we learned it? The short answer to this question is "Index Match was introduced fairly recently, and does everything that V/H lookup can do, but faster, more flexibly, and requiring no formatting on your part before using the function."
To get a little bit more specific, V lookup and H lookup give you the same result as Index Match (that is, they give you the associated value of an item that you're looking for in an array, this practice is called a lookup in the biz) but...
- The two lookup formulae require data to be formatted a certain way in order to work.
- V lookup can only look up a value within a column (vertically) and H-lookup within a row (Horizontally).
- The lookup formulae consume a lot more computing power (which actually becomes a big issue when you start dealing with potentially millions of values).
With that being said, many people (and you'll get there too) tend to "go with what they know works" and because index-match was introduced more recently, those that utilize the lookup functions never found the need to to learn a new function. However, there is literally NO benefit to using the lookups vs. using Index(Match)) and you can sometimes make a great impression on a mentor if they use lookups, and you can teach them index-match, or even better, if a job requires you to know V-lookups, you can now impress them by explaining the benefits of index-match.
Next time, we make like a startup and PIVOT