Bottom Line: Which is faster? Let's compare the speeds of two popular formulas that are used to look to the left.
Skill Level: Advanced
Download the Excel File
You can follow along and test for yourself using the same worksheet I use in the video.
Comparing Calculation Time
Sometimes Excel can be a little slow to calculate certain formulas and you wonder, “Is there a faster way to do this?” The great thing about Excel is that there are many ways to perform different tasks, so often the answer is yes.
One example of this is when you want to use a function to look up a value in a column and return a value from the matching row in a column to the left. One popular option is INDEX MATCH.
Returning a value in a column to the left is something VLOOKUP can't do on its own, but we can use a trick with nesting the CHOOSE function in the table_array argument to make it work.
When comparing the calculation times for very small amounts of data, they can seem somewhat equal and interchangeable. But as our data range increases in size, we will notice a much larger disparity, and one option becomes a clear winner in the race.
INDEX MATCH vs. VLOOKUP CHOOSE
For the test, I've set up one column to run the INDEX MATCH formula.
And in the column right next to it, we'll use the VLOOKUP CHOOSE formula.
The formulas are copied down 50,000 cells, which will give us a good performance test.
Timing the Calculations
Test #1
For the first test, I compared the calculation times between MATCH and VLOOKUP CHOOSE on a lookup table that is 500 rows of unsorted data.
INDEX MATCH took 0.195 seconds to calculate the 50,000 formulas.
VLOOKUP CHOOSE took 3.438 seconds, which is over 17 times slower.
Just to clarify, it's NOT vlookup that is causing the delay. The CHOOSE function is slowing things down.
When testing a regular VLOOKUP formula that looks to the right, it calculates in 0.184 seconds. Slightly faster than INDEX MATCH.
So, a regular VLOOKUP is just as fast as INDEX MATCH. The CHOOSE function is causing the slow down. We will see how this time increases with a bigger lookup table in the next two tests.
Test #2
For the second test, I ran the functions on a lookup table of 5,000 rows of unsorted data. As you can see, VLOOKUP CHOOSE was much slower this time. Over 255 times slower than INDEX MATCH or a regular VLOOKUP.
You might have also noticed that the VLOOKUP and MATCH functions calculated a bit faster than test #1, which had fewer rows in the lookup range. This is likely due to the sort order of the data. The data in test #2 is still unsorted, but in an order where the lookup values are found faster.
Test #3
For the final test, I changed the formulas to use whole column references.
Using whole column references allows you to reference the entire column with something like B:B, instead a specific range with rows like B9:B508.
The advantage to whole column references is you can add data to the column and not have to worry about changing the range reference.
Some functions are optimized for whole column references. However, the CHOOSE function is NOT. The results were not favorable.
It took 74 minutes for VLOOKUP CHOOSE to calculate the results that MATCH calculated in less than a tenth of a second.
Whole column references can also slow down some other Excel functions, so whenever possible, I would recommend avoiding them. Defined ranges or Excel Tables and structured references are a safer bet.
Why is CHOOSE So Slow?
I believe the CHOOSE function is having to load the values in the arrays (ranges) into memory every time it calculates. I'm not 100% certain on this theory but guessing this is why the performance slows down as the arrays get larger.
I'll see if I can confirm with some engineers on the Excel team.
Conclusion
So the results are clear, as you can see in this chart that compares our first two tests.
INDEX MATCH is a clear winner over VLOOKUP CHOOSE in terms of calculation speed. That's why I'm not a proponent of using CHOOSE and will always opt for MATCH. In fact, I can't think of any situation where CHOOSE would be a better choice.
How Does XLOOKUP Compare?
Microsoft just rolled out a great new function called XLOOKUP that is available to a limited amount of users. It will replace VLOOKUP someday (in the distant future) and it should minimize a lot of errors that we get with VLOOKUP today. Since XLOOKUP is still being tested and optimized, I will wait until it's completed to see how it compares. Stay tuned for that.
If you'd like to take a look at how it works, I've introduced and explained XLOOKUP in this tutorial: The New XLOOKUP Function for Excel.
Other Tests?
Can you think of other Excel functions that you'd like to test against each other? If so, let me know in the comments below. You can also leave questions or comments. Hope this information was helpful!
Great article Jon!
Thanks Jim! 🙂
In a large, multi-sheet model where input data was changing, I replaced Vlookup with Index-Match and the model ran much faster. I believe that Match only recalculates if the lookup_value or one-column table_array changes, whereas Vlookup recalculates if the lookup_value or anything in a multi-column table_array changes. True?
Also, one Match value can be used in many Index functions to pull several fields from a row in a table. Using Index in an array formula can return multiple adjacent fields from a row in a table.
For these reasons, and a few others, I see no reason to use Vlookup instead of the simpler and as-fast or faster Index-Match.
Hi Eric,
Yes, that is correct. VLOOKUP “can” be slowed down by formulas or dependent cells in the table_array that are not in the lookup or return columns.
Great point about reusing the MATCH function too.
The main reason I still use VLOOKUP is for the users of the file. If you are distributing a file to users that are familiar with VLOOKUP, but not INDEX MATCH, then you might have to explain the formula or revert back to VLOOKUP. I have had this happen several times in my career. VLOOKUP is the 3rd most used function in Excel. So, it is relatively well known and understood by more users.
Of course, I’m all for educating other users on INDEX MATCH, when it makes sense to do so. Sometimes the boss asks for VLOOKUP, and that just ends the discussion… 🙂
I hope that helps.
You might want to consider writing a huge warning that we need to turn on *manual calculation on our local Excel* before trying to open your sample workbook… It’s taken several minutes and still only calculating on 8 threads at only 29% for me. Excel 365 and a fairly high-end PC too.
Thanks Gary! I’ve updated the file based on Colin’s suggestion and the formulas are only in the first row now. It should open faster even if Excel is in Automatic calculation mode.
Hi Jon,
It might be more efficient to have the formulas in just the first cell of each column. It’s taking a very long time to load up the workbook and it keeps crashing with all the formulae already in place.
Thanks
Thanks for the suggestion Colin! I’ve made that change to the file now and it should open much faster.
My apologies to anyone that had issues opening it.
Quick Question: the CHOOSE part of the formula CHOOSE({1,2} appears like an array, but Ctrl+Shift+Enter doesn’t work half-way through a formula and must be performed at the end. Does that mean there is no short-cut to applying the curly braces, other than manually typing them in each time?
Thanks
Great question! The curly brackets are used for an array, but it’s a different type of array. In this example with CHOOSE, the array is a list of values {1,2}.
The curly brackets in a Ctrl+Shift+Enter array formula just help denote it is an array formula.
I hope that helps.
Jon, I see you’ve chosen to not include my earlier comment about warning users to make sure to set manual calculation first. Maybe that’s not the issue, I don’t know for sure since I’ve had to use Task Manager to shut down Excel. But once I click “Enable” content it just crawls. It’s unusable due to the extreme length of time it’s taking to open this workbook, and Colin is making the same observation.
You’ll be finding that you’re alienating readers if you don’t address this somehow.
Hey Gary,
I’m not ignoring you at all. I just hadn’t seen your comment yet. The file has been updated and I apologize for any inconvenience it has caused.
Thanks again and have a nice day! 🙂
I used this as a speed test for work PC. Mgt says I have fastest PC in house. IF they get me a new one, I will use this as a benchmark.
Very last question Jon..promise lol
Does the VLOOKUP-CHOOSE work if columns are inserted/deleted?
I was thinking that it’s similar to INDEX-MATCH in that you select the return range and lookup range separately, which is the specific reason that inserting/deleting columns has no effect. With VLOOKUP-CHOOSE, you’re only ever choosing two ranges in this formula, irrespective of how far the lookup and return ranges are apart e.g., =VLOOKUP(J6,CHOOSE({1,5},$A:$A,$E:$E),5,0). Am I correct in my thinking?
I liked your set-up of the graph in the downloadable workbook and how the organisation of the small table informed the labels on the graph. Do you have any videos in the pipeline for best practices on how to get the most out of certain graphs for different business cases and to maximise presentation? I know many people who are good with many thing in excel including VBA, but are weak on graphs and achieving that visual impact.
The VLOOKUP with COLUMNS was an excellent idea as well .
Thanks
Pt.2
In my job, I need to use VLOOKUPS between different workbooks, which can sometimes add extra complexity to a formula. When I first downloaded your INDEX-MATCH vs. VLOOKUP workbook, the INDEX-MATCH was all based on the same sheet. I soon discovered how to change this to match on another workbook.
For example, =INDEX(A$1:A$6,MATCH(G6,C$1:C$6,0))- the first range (A$1:A$6) is the return range, G6 is the lookup value chosen on the same page as the formula being typed and the second range (C$1:C$6) is the lookup range located in the second workbook.
I was wondering how this could be applied to VLOOKUP-CHOOSE? To extract values from a different workbook would this read correctly? =VLOOKUP(G7,CHOOSE({1,2},[Book1]Sheet1!C$1:C$6,[Book1]Sheet1!$A$1:$A$6),2,0
I’m choosing the lookup value G7 from the workbook where I am typing the formula, the first and second ranges 1)being the lookup range and 2) being the return range are both located in the second workbook?
If I were thinking about this in football/soccer terms INDEX-MATCH and VLOOKUP-CHOOSE across two different workbooks would score 2-1 in terms of how many ranges are referred to on the second workbook compared to the workbook you are actually typing the formula into?
Thanks
Hey Colin,
No worries! 🙂
You are correct. The VLOOKUP CHOOSE does allow for columns to be inserted and deleted because it is referencing the lookup and return ranges separately.
Based on the performance and complexity of the formula, I’d still say avoid it. INDEX MATCH has the same benefits. But it is good to understand how the VLOOKUP CHOOSE formula works.
Great suggestion on future articles for best practices on chart types. I’ll add it to the list. We do have training on this in the Elevate Excel program as well.
Happy to hear you enjoyed the VLOOKUP with COLUMNS post. Thanks again for your support! 🙂
Hi Jon. I have not been able to find anywhere how structered tables references instead of a column range. Are you able to shortly give an example of how a formular looks like. Thanks. Regards Michael, Denmark
Hi Jon. Sorry about the missing words in the last question etc.
I See that I did not say that I was searching how to ude index match for lookups in a table by using structured references and table hearders (if that is possible). Hope to hear from you. Michael, Denmark
Hey, I just googled ‘vlookup vs index match’, and this was among the top results, nice SEO job and nice article.
I downloaded the sheet and tried to test it on LibreOffice Calc.
I did pretty much the same you did on your video, with the difference that I had to fill the formulae from row 7 onwards beforehand. But when I select them (for instance, column F row 7 to end) and click ‘Calc’, the results appear instantaneously and the result time is 0.
What might I be doing wrong? Any chance you (or someone else reading this) can test it on LibreOffice Calc?
hile there were no apparent errors with the calculation itself,
Regards
How do you create the chart that compares our first two tests.
Pls post videos regarding above chat method.
How about the combination of Vlookup and match? I think that is better.
Match is also slow. Index is the fastest command.
Do you know why CHOOSE in that way doesn’t work on Google Spreadsheets? ex. =vlookup(B27,choose({1,2},$B$25:$B$30,$A$25:$A$30),2,false)
Ran my own test using 50000 simulation trials removing a number of Choose command — actually eliminating all. The VLOOKUP command is very slow, so I question your conclusions. Happy to share the timing model.