My husband and I spent an entire evening arguing over which was better – VLOOKUP or INDEX-MATCH. To be fair, understanding INDEX with MATCH takes more time for some users to grasp than VLOOKUP, but the value of this function combination for diverse lookup scenarios cannot be understated, and is, in my opinion, the better choice. INDEX with MATCH provides a more versatile, straightforward construction syntax than VLOOKUP for range reference edits and complex lookups. Looking up values by case sensitivity character count first or last numeric value positive or negative numbers absolute values partial strings row and column header labels the options are enormous with INDEX and MATCH that VLOOKUP and HLOOKUP do not handle efficiently, if at all. Lookups can be much more complex than for generic text. VLOOKUP and HLOOKUP are useful functions for basic lookup situations.Ĭombining the INDEX and MATCH functions provides you with everything VLOOKUP does, and more. VBA’s VLOOKUP worksheet function is intuitive for programmers. VLOOKUP operates well in either direction: lookup value at left returning data at right, or vice versa with a nested array constant or CHOOSE function. VLOOKUP’s syntax is a single-function construction with four arguments (one is optional), and it’s a popular function for the world’s millions of Excel users who need a basic lookup tool for their work. It depends on what you are looking up, what you expect the formula to return, your familiarity with Excel’s nested functions, and if you care about tiny differences in execution times (INDEX and MATCH are faster), that can become noticeable in very large calculation projects. I’m not going to drive to the store to get proper fly poison. If there is a fly in my home, I’ll smash it with a newspaper, a shoe, or a paper bag. In my work, INDEX-MATCH-MATCH has proven far more valuable than INDEX-MATCH.īottom line for me: these are just tools and I’m looking to get a task done. In that case, I’m using VLOOKUP and skipping the syntax and jungle of parentheses with INDEX-MATCH.Īlso, with the introduction of Get & Transform, I might skip the VLOOKUP and use a Left Outer Join. After I’ve retrieved the data I’m going to Copy/Paste-as-Values.A range of 50 rows and 5 columns of data that need to retrieve data from a lookup-range that’s 200 rows and 6 columns.
However, for my usual purpose, the merits are irrelevant. The merits of INDEX-MATCH over VLOOKUP have been established. Lots of talk, lots of evidence, lots of facts, very little persuasion. This has become the Excel community’s version of political debates.
0 kommentar(er)
