XLOOKUP vs. INDEX MATCH - Which is faster?

Ever wonder what is the fastest lookup function in Excel? In this experiment style video, I test VLOOKUP vs. INDEX+MATCH vs. XLOOKUP by writing 11 million formulas. The results are surprising!!!
Note: there is no sample file for this video (the dataset is rather large, but feel free to make some random data in Excel to test the concepts yourself).
⏱ In this video:
=============
0:00 - Fastest lookup formula in Excel!
0:26 - Dataset and testing methodology
2:31 - Writing the VLOOKUP formula
3:34 - INDEX MATCH formula
4:34 - Faster INDEX MATCH formulas
6:32 - XLOOKUP formula
7:16 - The results (calculation time)
10:51 - File size comparison
11:48 - What does it all mean?
🧪💻About my computer:
======================
Any test results should be viewed in conjunction with the equipment used. So here is my computer configuration.
Operating System: Windows 10
Office version: Excel 365 Insider Beta channel (version 22xx)
Processor: Intel i5 @ 2.90 ghz
Ram: 16 GB
GPU: Dedicated GPU (Nvidia Quadro P400 2GB)
🏃‍♂️FAST Excel Add-in:
=================
If you need to profile, audit or speed up your workbooks, definitely check the Fast Excel add-in. www.decisionmodels.com/FastEx...
#notsponsored
Other ways to combine data
=======================
Lookups are not the only way to combine two tables of data. You should try below options to speed up or simplify your data combines.
◉ Combine two tables with Power Query - Video: • How to connect two tab...
◉ Using data model and relationships (Power Pivot) Article: chandoo.org/wp/introduction-t...
◉ Using SQL
Learn more 😎
============
👌 Must have Excel formulas:
XLOOKUP - • I don't use VLOOKUP an...
FILTER - • I don’t use filters in...
IF - • 5 Advanced Tips on how...
SUMIFS - • These are the ONLY 15 ...
Top 10 formulas - • Learn these top 10 Exc...
😍 Other Excel features:
Conditional Formatting - • 5 Conditional Formatti...
Power Query - • Power Query Tutorial -...
Pivot Tables - • How to use Pivot Table...
#vlookup

[@utubeAgape]

Since XLookup went live, I never have gone back to VLookup or Index Match! Less time and less key strokes!

[@chandoo_]

XLOOKUP for the win!!!

[@ManiGupta49]

Same here! I also recommend my team mates to use XLOOKUP instead of VLOOKUP.

[@sahil.dahiya]

I like xlookup just cz I am lazy 😂🙂

[@Excelambda]

Super mega video!! Excel formulas on a racetrack!! Is cooler than F1 😉 Binary search is the fastest way for both XMATCH/XLOOKUP if lookup array is in ascending/descending order. Is lighting fast. To "feel" the difference, a very simple test anybody can do: in A2 : =SEQUENCE(1000000) (1 million) in B2: =RANDARRAY(1000000,,1,1000000,1) in D2: =XMATCH(B2#,A2#) takes looooooong time then test this =XMATCH(B2#,A2#,,2) considerably faster So every time we can sort lookup array (ascending or descending) binary search makes a huge difference using binary arguments (2 or -2) It would be very nice if you could time these two formulas. And again, I loved the "race" . ✌✌😉😉🙏🙏

[@stevenlagoe7808]

'Cooler than F1'... I love it! 😆

[@Excelambda]

@@stevenlagoe7808 🙏😆

[@chandoo_]

Of course, sorted data makes lookups insanely faster.

[@pgkannan]

Thank you

[@sakibhossain3761]

You've such a talent...I've lesrned so many things about excel from your Videos in an interesting way...

[@chandoo_]

You are welcome Sakib 😎

[@spinPX]

Thank you and very interesting to see. Do you have some tips which steps should be done to identify slow formulas/calculations and how to improve the hierarchy and speed of my excel file ?

[@canirmalchoudhary8173]

XLOOKUP is the ultimate lookup formula and very much due to spilled array and moreover power query is just there to help in lookup in the fastest way vis a vis excel. But what I observed from my colleagues that when they show their working using XLOOKUP, managers get shocked and say that don't use XLOOKUP, it is not the right way and party will not accept the solution

[@mghector6430]

You are genius Sir🙏. Learning so many things from your videos 👍

[@chandoo_]

You are welcome MG H.

[@BalrogsHaveWings]

Thanks to you and Leila, I'm a convert from IndexMatch to XLookup. However!... As with anything, if your lookup data is garbage, IndexMatch appears to have some kind of edge... I need to do some more research to find out exactly why.

[@Azhar_Khan383]

amazing, awesome, wonderful. Thanks Sir

[@chandoo_]

Most welcome

[@supreethsharma4042]

Very informative

[@RenierWessels]

Thanks Chandoo. Great video!

[@chandoo_]

My pleasure!

[@balkrishna83]

Sooo goooood video

[@chrism9037]

Great video Chandoo! I almost exclusively use XLOOKUP now

[@chandoo_]

Same here!

[@rajeshchawda9449]

Thank you Chandoo for this Awesome TEST video. Which Screen Recording Software did you use? Which one is a good screen recording software available ? Are these screen recording software(s) always paid versions?

[@teachtigers5710]

You made me the day Great Chandoo Sir. Love from Madhu, Hyderabad

[@chandoo_]

You are welcome TT 😀

[@anv.4614]

You are so wonderful. Warm greetings from Germany. Thanks

[@chandoo_]

Thank you An V. So are you 😎

[@taizoondean689]

Thanks for the video. Sir can you please create video on how to use add which you have shared along with other excel add ins and it's usage

[@chandoo_]

You are welcome TD. Thanks for the suggestion. I will talk about my VBA experiences and recommendations in a future video.

[@VisuLytics]

Chandoo G...Very very helpful comparison....thanks a lot

[@chandoo_]

My pleasure

[@lianbaite1995]

Wonderful content. always look up to your videos. could you also mention the storage type you're using...is it hdd or ssd

[@chandoo_]

Thanks Lian. I am using SSDs

[@teoxengineer]

So significant and profession video. Thank you Chandoo. Have you tried it for VBA? I guess it will take more less time than other formulas

[@chandoo_]

You are welcome Emre. Hmm.. If we use worksheetfunction.vlookup or .xlookup, the timing should be same (or more!) in theory. I haven't tested with VBA though as VBA tends to _hang_ more when you throw lots of data.

[@moesadr3342]

Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use XLOOKUP up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? How would the formula could look? Do you know a better solution without NO VBA? Thank you!

[@sriharim1596]

Greate testimony. would like to know along with Data model / power query feature. Please share the info on those as well

[@chandoo_]

If possible, you should use the PQ or Data Model approaches. They can take more data without significantly slowing Excel down.

[@pjpatel133]

When I select multiple entire rows or columns in excel, it does not highlight or shaded gray. Observing this issue since last few months. I tried hard to fix this, but couldn't find the solution. This is only for my excel, my colleges have no issue in selecting the rows/columns. If have a chance to reply, I would greatly appreciated. Thanks.

[@basicinfoforall7306]

Thanks bro! I faced a problem that was lost link with data as i thought for net and my laptop's low configuration . My laptop should be like this configuration. One problem is my country-Bangladesh.Very slow is net connection. Please let me give a solution.It will be great help for me.

[@Bhavik_Khatri]

I think Power Query would be better then using Excel Formulas. I must say early this year I would have using only Excel formulas. This exercise is specific candidate for Power Query.

[@chandoo_]

Of course PQ (or even something upstream like SQL) would be my first choice for such a job.

[@muhammadtambawala6379]

The only reason I don’t want to move away from Index Match is because when I press “Ctrl+[“ in an index match formula it takes me directly to the precedent(s) column/row whereas in vlookup and xlookup I have to read the formula to understand.

[@hussainfawzer]

this is super useful

[@007dpk]

Hi Chandoo, love your videos. I just wanna know whether or not it's possible to use look functions with multiple criteria,if yes , then please make a tutorial on how it's done.

[@LoveYourFamily2]

I would use =index(column_that_you_need_results_from, match(1,index((range1=criteria1)*...*(rangeN=criteriaN),0,1),0),1) Since this formula uses boolean logic, in the place of "=" sign you can use > or < as well. For this reason, i prefer this function. However, slow on large datasets. Could probably Power query or Power pivot be a solution? Another way to do this with formula, is to combine values in vlookup, however, i think it is limited, because i have not found a way to use < or > in this formula. vlookup(criteria1&criteria2, table_with_combined_values_in_first_column, number of columns, 0)

[@NathanJohnsonNaterspotaters]

=XLOOKUP (1,([CRITERIA1] = [CRITERIA1])*(CRITERIA2] = [CRITERIA2]),[return column]) or =XLOOKUP (([CRITERIA1]&[CRITERIA2]&[CRITERIA3], [CRITERIA1]&[CRITERIA2]&[CRITERIA3],[return column])

[@chandoo_]

Thanks Deepak. You can also use FILTER to do this easily. See this video for that - kzbin.info/www/bejne/gKa3lZ1on5Jsgck

[@chandoo_]

Good one there Baiba... Donut for you 🍩

[@benclips]

Hi Chandoo...do you have a video on how to use XLOOKUP with multiple criteria?

[@deegadams]

I am watching from my phone, I just want to make sure I am seeing it correctly. For the return value you can select all the columns you want to return & it will spill over each respective columns data? For X lookup

[@chandoo_]

YES!!! XLOOKUP can spill 😀

[@ragkrishna]

You should also give the configuration of the computer you use, a desktop or a laptop so that it becomes helpful for anyone who really have to work with loads of data.

[@chandoo_]

Refer to the video description. It has been already mentioned.

[@DinoDelight]

Thanks for this just confirms xlookup is the way to go, is there a similar video comparing SUMIF or any alternative speeds?

[@chandoo_]

You are welcome TLB. For large volumes of data, I use Pivots rather than SUMIFS or any variations. I have not noticed any speed diff with smaller datasets though.

[@DinoDelight]

@@chandoo_ thanks for the reply, a colleague mentioned issues using sumifs on large amounts of data, haven't seen the worksheet yet but thought I'd get ahead before I take a look at it. Thanks again

[@MakeatHome]

Hi I’m learning excel through your videos. The problem is the formulas are not available in office home and student version is this right is there an alternate way to learn these thank you

[@chandoo_]

Hi there.. you can test out most formulas on excel on the web. Try excel.new in your browser.

[@MakeatHome]

@@chandoo_ thank you. Will check

[@vinaykumar-sl2ix]

I use all 3 formulas. But my current favourite is xlook up. I can do many different stuffs

[@chandoo_]

XLOOKUP or ALT+F4

[@vinaykumar-sl2ix]

@@chandoo_ XLOOK UP. thankyou for your reply.

[@ysantosh]

Nice informative video again CHANDOO anna

[@chandoo_]

You are welcome Santosh 😊

[@creatorkannan675]

I need this sample work book with the formula for my learning

[@chandoo_]

Feel free to make something up. As the files are very large, I won't be sharing them.

[@Excelambda]

As Chandoo said, the file can get "heavy" , but it is not so complicated to create your own. - 1st clm single cell formula: ="PC - "&TEXT(SEQUENCE(nr rows,,starting index),"0000000") (nr of rows: 1,000,000 and starting index: 999 in our case) - string clms single cell formula: =BYROW(CHAR(RANDARRAY(nr rows,nr letters,65,90)),LAMBDA(x,CONCAT(x))) if you do not have BYROW , cell formula with fill handle: =CONCAT(CHAR(RANDARRAY(nr letters,,65,90))) nr letters: nr. of chars/string (10 in our case) If you want lower case you can add =LOWER(...previous formula...) - random dates clm, single cell formula example: =RANDARRAY(nr rows,,"1-Jan-2000","31-Dec-2022",1) - random values clm in general, single cell formula example: =RANDARRAY(nr rows,,any min value, any max value) In 5 min you are done. And if you want the values to not recalculate every time, copy them as values on other spreadsheet. ✌

[@chaiyya345]

I just got MS 365 with XLOOKUP & with this new features i hardly use index&match. XLOOKUP all the way

[@chandoo_]

XLOOKUP!!!

[@reachraheem]

Hi Chandoo... Love from Bangalore.... My question is about Bringing data from AWS S3 to Power bi video you made... That's an amazing video... My concern is i want to import all CSV files from S3 bucket in one shot.. your video showed only to import specific CSV file... Please please please help me.. I have posted a comment on the original video as well... Thank you in advance....

[@chandoo_]

Hi JAT...You can use R script I showed in that video as a baseline and enumerate all the items in the bucket and loop thru. R has some excellent packages to combine data from CSVs. I would just use that and load one data frame to Power BI.

[@reachraheem]

@@chandoo_ thank you very much for your reply, Chandoo..... I tried searching through internet but didn't find any relevant information.... All i found is to import a single CSV ... Please share any link of you come across.... Thanks again

[@chandoo_]

You should try writing the code yourself. I outlined the approach in the above comment. I will not be writing the code or making another video on this topic.

[@janardhanreddy9836]

Really xlookup very powerful compare with other formula

[@chandoo_]

IT IS!!!

[@vinodkumar-io3fu]

hi sir I m new with zero knowledge of excel can you guide me from which video should I start

[@chandoo_]

Start with this - kzbin.info/www/bejne/fGjEgZSwqrhrh7s

[@taizoondean689]

Also sir can you create video on power Query or power BI extracting data from Twitter or FB

[@chandoo_]

I recently made one video about web extraction. Check it here - kzbin.info/www/bejne/onfbYYChrqeNqck

[@micheltw]

Can you try with if error for index match and the error capture for xlookup

[@chandoo_]

Hmm.. I tried only for XLOOKUP with 8200 product codes missing (out of 1,000,000) and with a few browser tabs open and some other stuff running, it clocked about 13 seconds. I also didn't use the FastExcel add-in to measure this as I uninstalled it soon after recording the video. I just measured the time with my cell phone timer.

[@micheltw]

@@chandoo_ 👍 thanks for this test

[@gurumoorthy5282]

Hi all i am facing one problem in excel in that Excel cell values are there formula bar value shows But in cell its not show

[@mymatemartin]

One suggestion to begin with... Format the cell using the General format style. It might help.

[@evilangel4136]

what is the type and generation of i5 processor?

[@chandoo_]

I have i5-9400F

[@asif_786]

xlook is awsm why use vlook up and index match ?

[@chandrakantha648]

Chandoo, can we totally forget other formula and totally rely on XLookup ?

[@sudardurai6569]

Hi Chandoo I'm writing this in a belief that you will reply and give me a solution on my issue . I'm trying to calculate the maximum number of profit and loss streaks in excel but I couldn't get the right formula or method to solve this. I'm trying to solve this for more than 6 hours😔😔. Kindly help me out in this issue.

[@chandoo_]

See this for some ideas - chandoo.org/wp/longest-winning-streak-problem/

[@Shashankbaranwal]

Hey you just forget to freeze return array in xlookup🤔

[@RH-ko6pz]

This is really interesting - could you do some testing around the volatility of the formulas? If I understand correctly INDEX MATCH will only calculate if you make a change to the column referenced whereas VLOOKUP will calculate if there is any change to the entire range. This is why I avoid VLOOKUP. Where does XLOOKUP sit in this?

[@endthefed9937]

I use xlookup almost exclusively but for the life of me I cannot figure out why I still get spills errors attempting this

[@chandoo_]

Is there anything on the righthand side of the xlookup that is preventing the spill? If you select the spill error, it will highlight the range Excel wants to spill and you can spot the mistake.

[@0933637118]

Could you give me the data you used please..

[@chandoo_]

The files are large, so I can't share them. Please make up some random data using RANDARRAY or RANDBETWEEN formulas.

[@birpalkandari3084]

Hi

[@AliensTech]

INDEX MATCH is way ahead of LOOKUP .

[@chandoo_]

Did you watch the full thing? you will be surprised.

[@AliensTech]

@@chandoo_ I did. I mentioned interms of complexity. You are right with large data collections.