Wyn Hopkins的动态

I wish this dumb debate would stop - it’s perpetuated by those ignorant of what Excel offers. As mentioned in the comments above, the comparison of an INDEX/MATCH combo formula with a naked VLOOKUP is like comparing apples with oranges - naive, unfair, flawed! Almost all "perceived" advantages of the former are based on specious arguments! ???? Using MATCH, COLUMN, or COLUMNS functions can overcome the constant value most inexperienced users plug into a VLOOKUP. Using CHOOSE within VLOOKUP can make it look to the left or right AND AT THE SAME TIME eliminate the need to reference a whole swag of columns that add no value, thus mirroring the INDEX/MATCH combo of referencing just the lookup column and return value column. End of argument. Game over.

According to me, I choose Index Match.

It depends on how you use it, they r all useful referencing formulas. Index match gives a more accurate answer compare to Vlookup depending on how your source structured

Vlooluo is volatile and personally I never use v or h lookup. Index and match every time

I've been a hard-core Excel developer since the 2.0 release back in 1987. I have NEVER used VLookup or IndexMatch for any reason other than prepping for certification exams. Of course, I'm primarily a SQL Server/ Azure DB / Access developer, and strongly believe data management belongs there and not in Excel.

I strongly agree with the majority. Index Match is way more power it is much faster and uses less resources than Vlookup. Index match is extremely dynamic and multifaceted. It has the ability to conform to your formula needs, it is more flexible, multidirectional and most importantly it is a lot more accurate. It requires less 'prep' work than Vlookup. It your column in any direction then seamlessly find the row that intersects it without using any absolute hard coded ranges. No more counting column numbers. Once you use it and see all that it is capable of doing, you'll likely never use Vlookup again. I agree that index match may be suited more for power users however it can also be very simplistic if you need. I have calculation time comparisons with a columns pulling remote data using both vlookup and index match. Index match was much faster.

I can’t help feeling that today’s vlookups and indexmatches are tomorrow’s excel silos that a successor may struggle to understand. Then again, I was never much good at indexmatch!

Wyn Hopkins, VLOOKUP can do all below tricks if you boost it powers by nesting another function.? As Oz du Soleil, pointed out we are comparing two functions ( INDEX & MATCH) against 1 function (VLOOKUP) When you battle VLOOKUP against INDEX or against MATCH individually, vlookup comes way on top. *VLOOKUP Can do a Left Lookup *VLOOKUP Can Return Multiple columns in a Lookup *VLOOKUP Can do a Multiple Criteria lookup *VLOOKUP Can handle the insertion and deletion of columns in the lookup range https://crispexcel.com/vlookup-is-dead-or-is-it/

This may be an analogy too far but lets think of VLOOKUP as a screwdriver and INDEX MATCH as a power drill...? ?wait...wait.... I'm not saying INDEX MATCH is faster than VLOOKUP that isn't what this analogy is leading to.? ? ? If? I need to screw something together I just pick up the screwdriver and hey-presto done.? ? Why would I bother unpacking the Power Drill, plugging it in and finding the right toolbit?? ?? Then I need to drill a hole.? ?I then need to get the Power Drill out. That is Oz's context view. For me, something changed several years ago? They invented the cordless drill that never needs charging and made it as easy to carry as a screwdriver. This came about as the result of 2 things 1.? Tables were introduced in Excel 2007 with it's structured referencing. 2.? I came up with an autocorrect trick to help write the INDEX MATCH formula? https://www.dhirubhai.net/pulse/stop-using-vlookup-wyn-hopkins/ So for the last 10 years,? regardless of context, I pick up my cordless drill.? The screwdriver is just gathering dust. Once in a while I might need a screwdriver to open a tin of paint though.... :)