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 learn a new function. However, there is literally NO benefit to using the lookups vs. using Index(Match)) 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.
