Excel Performance Test: Vlookup Vs Index Match

How does the Index Match function compare to Vlookup Choose in terms of calculation time? I ran a series of tests to compare the two and found that there is a very clear winner.

I ran three tests, with the formulas in 50,000 cells and changed the size of the lookup table for each test.

--Test #1: Match vs. Choose with a lookup table of 500 rows 0:15
--Test #2: Match vs. Choose with a lookup table of 5,000 rows 2:25
--Test #3: Match vs. Choose using whole column references 4:20

If you’d like to see me test and compare other functions and formulas, please leave a comment below.

Additional Resources:

#MsExcel #ExcelCampus
00:00 Introduction
00:10 INDEX MATCH vs. VLOOKUP CHOOSE
00:39 Timing the Calculations

[erin]

So helpful. I appreciate the analysis and suggestion.

[wayneedmondson]

Hi Jon.. thanks for the performance comparison. Nice to keep in mind that while there are multiple ways to solve problems, there can be a performance penalty for selecting one method vs. another. Thanks for the insights. Thumbs up!
PS - I downloaded the workbook so I could see how you set up the timer routine.. simple and elegant. Thanks for that example also.

[naifal-amodi]

Thank you Jon as usual
I have a question regarding conditional formatting
For example
If cell A2 is yes >> then format cell A1 with green
This is easy
My question is, ‏ is there any way to do this condition for cell B2 to ZZ2 easily rather than applying it many times?

[arsalanshahid]

can you share how you made that macro for calculating calculation time?

[educational]

Thankyou for your video.. But why my vlookup choose can't work. Its work normally when i changed the comma to backslash (choose({1\2};, , , )

[ChrisLim-ifrk]

What is the command for calculating speed?

[aliaydogan]

I have the same problem with vlookup + match. I will try to change it to index + match. I also could not find any proper information anywere about how excel uses computers. Anyone knows what is important on a computer for better excel performance? I am using macbook air m1, 8 gb ram and 256 ssd. I often end up crashed excel while working on pivot tables with vlookup + match, (including, data sources are 4 different sheet, pivot tables with vlookup + match formulas are 10 different calculation sheet, each calculation sheet is around 10.000 rows, the excel workbook is 40 MB). I checked activity monitor during calculation. I noticed cpu is working with 100% during calculation. As I know excel uses single core. Is it right, or excel uses multi core? So, If I change my laptop to any other laptop what should I care more? A cpu with more core or a cpu with higher clock speed or higher ram? According to activity monitor my cpu is the weak chain. What would you recommend if you had any experience? Thanks.

[rogerthat]

We need our IT to upgrade us so we get XLOOKUP in our excel! Index&Match has been pretty good for me in the meantime.

[MN-besx]

Need your help to design a formula to get a unique code in excel, for example I have “Received” word in cell A1 and I want to build a unique code in B2 cell, formula result should capture “R” then today’s date like 290919 (29 day, 09 month & 19year) then add two random numeric digits to make these codes unique and non-duplicate. Code should looks like R29091932 (32 is random numeric digits). I have thousands of entries and results in B2 columns must not duplicate with all above codes. Thank you.