Since XLookup went live, I never have gone back to VLookup or Index Match! Less time and less key strokes!
@chandoo_2 жыл бұрын
XLOOKUP for the win!!!
@ManiGupta492 жыл бұрын
Same here! I also recommend my team mates to use XLOOKUP instead of VLOOKUP.
@sahil.dahiya2 жыл бұрын
I like xlookup just cz I am lazy 😂🙂
@Excelambda2 жыл бұрын
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" . ✌✌😉😉🙏🙏
@stevenlagoe78082 жыл бұрын
'Cooler than F1'... I love it! 😆
@Excelambda2 жыл бұрын
@@stevenlagoe7808 🙏😆
@chandoo_2 жыл бұрын
Of course, sorted data makes lookups insanely faster.
@chrism90372 жыл бұрын
Great video Chandoo! I almost exclusively use XLOOKUP now
@chandoo_2 жыл бұрын
Same here!
@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.
@mghector64302 жыл бұрын
You are genius Sir🙏. Learning so many things from your videos 👍
@chandoo_2 жыл бұрын
You are welcome MG H.
@canirmalchoudhary81732 жыл бұрын
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
@sakibhossain37612 жыл бұрын
You've such a talent...I've lesrned so many things about excel from your Videos in an interesting way...
@chandoo_2 жыл бұрын
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 ?
@teoxengineer2 жыл бұрын
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_2 жыл бұрын
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.
@muhammadtambawala63792 жыл бұрын
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
@rajeshchawda94492 жыл бұрын
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?
@007dpk2 жыл бұрын
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.
@LoveYourFamily22 жыл бұрын
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)
Thanks Deepak. You can also use FILTER to do this easily. See this video for that - kzbin.info/www/bejne/gKa3lZ1on5Jsgck
@chandoo_2 жыл бұрын
Good one there Baiba... Donut for you 🍩
@benclips Жыл бұрын
Hi Chandoo...do you have a video on how to use XLOOKUP with multiple criteria?
@taizoondean6892 жыл бұрын
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_2 жыл бұрын
You are welcome TD. Thanks for the suggestion. I will talk about my VBA experiences and recommendations in a future video.
@deegadams2 жыл бұрын
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_2 жыл бұрын
YES!!! XLOOKUP can spill 😀
@chaiyya3452 жыл бұрын
I just got MS 365 with XLOOKUP & with this new features i hardly use index&match. XLOOKUP all the way
@chandoo_2 жыл бұрын
XLOOKUP!!!
@Azhar_Khan3832 жыл бұрын
amazing, awesome, wonderful. Thanks Sir
@chandoo_2 жыл бұрын
Most welcome
@teachtigers57102 жыл бұрын
You made me the day Great Chandoo Sir. Love from Madhu, Hyderabad
@chandoo_2 жыл бұрын
You are welcome TT 😀
@ragkrishna2 жыл бұрын
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_2 жыл бұрын
Refer to the video description. It has been already mentioned.
@balkrishna832 жыл бұрын
Sooo goooood video
@RenierWessels2 жыл бұрын
Thanks Chandoo. Great video!
@chandoo_2 жыл бұрын
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
@VisuLytics2 жыл бұрын
Chandoo G...Very very helpful comparison....thanks a lot
@chandoo_2 жыл бұрын
My pleasure
@pgkannan5 ай бұрын
Thank you
@anv.46142 жыл бұрын
You are so wonderful. Warm greetings from Germany. Thanks
@chandoo_2 жыл бұрын
Thank you An V. So are you 😎
@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!
@sriharim15962 жыл бұрын
Greate testimony. would like to know along with Data model / power query feature. Please share the info on those as well
@chandoo_2 жыл бұрын
If possible, you should use the PQ or Data Model approaches. They can take more data without significantly slowing Excel down.
@DinoDelight2 жыл бұрын
Thanks for this just confirms xlookup is the way to go, is there a similar video comparing SUMIF or any alternative speeds?
@chandoo_2 жыл бұрын
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.
@DinoDelight2 жыл бұрын
@@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
@supreethsharma40422 жыл бұрын
Very informative
@pjpatel1332 жыл бұрын
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.
@basicinfoforall73062 жыл бұрын
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.
@vinodkumar-io3fu2 жыл бұрын
hi sir I m new with zero knowledge of excel can you guide me from which video should I start
@chandoo_2 жыл бұрын
Start with this - kzbin.info/www/bejne/fGjEgZSwqrhrh7s
@chandrakantha6482 жыл бұрын
Chandoo, can we totally forget other formula and totally rely on XLookup ?
@Bhavik_Khatri2 жыл бұрын
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_2 жыл бұрын
Of course PQ (or even something upstream like SQL) would be my first choice for such a job.
@reachraheem2 жыл бұрын
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_2 жыл бұрын
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.
@reachraheem2 жыл бұрын
@@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_2 жыл бұрын
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.
@MakeatHome2 жыл бұрын
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_2 жыл бұрын
Hi there.. you can test out most formulas on excel on the web. Try excel.new in your browser.
@MakeatHome2 жыл бұрын
@@chandoo_ thank you. Will check
@asif_7862 жыл бұрын
xlook is awsm why use vlook up and index match ?
@taizoondean6892 жыл бұрын
Also sir can you create video on power Query or power BI extracting data from Twitter or FB
@chandoo_2 жыл бұрын
I recently made one video about web extraction. Check it here - kzbin.info/www/bejne/onfbYYChrqeNqck
@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?
@creatorkannan6752 жыл бұрын
I need this sample work book with the formula for my learning
@chandoo_2 жыл бұрын
Feel free to make something up. As the files are very large, I won't be sharing them.
@Excelambda2 жыл бұрын
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. ✌
@sudardurai65692 жыл бұрын
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_2 жыл бұрын
See this for some ideas - chandoo.org/wp/longest-winning-streak-problem/
@vinaykumar-sl2ix2 жыл бұрын
I use all 3 formulas. But my current favourite is xlook up. I can do many different stuffs
@chandoo_2 жыл бұрын
XLOOKUP or ALT+F4
@vinaykumar-sl2ix2 жыл бұрын
@@chandoo_ XLOOK UP. thankyou for your reply.
@Shashankbaranwal Жыл бұрын
Hey you just forget to freeze return array in xlookup🤔
@micheltw2 жыл бұрын
Can you try with if error for index match and the error capture for xlookup
@chandoo_2 жыл бұрын
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.
@micheltw2 жыл бұрын
@@chandoo_ 👍 thanks for this test
@gurumoorthy52822 жыл бұрын
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
@mymatemartin2 жыл бұрын
One suggestion to begin with... Format the cell using the General format style. It might help.
@endthefed99372 жыл бұрын
I use xlookup almost exclusively but for the life of me I cannot figure out why I still get spills errors attempting this
@chandoo_2 жыл бұрын
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.
@janardhanreddy98362 жыл бұрын
Really xlookup very powerful compare with other formula
@chandoo_2 жыл бұрын
IT IS!!!
@ysantosh2 жыл бұрын
Nice informative video again CHANDOO anna
@chandoo_2 жыл бұрын
You are welcome Santosh 😊
@evilangel41362 жыл бұрын
what is the type and generation of i5 processor?
@chandoo_2 жыл бұрын
I have i5-9400F
@birpalkandari30842 жыл бұрын
Hi
@AliensTech2 жыл бұрын
INDEX MATCH is way ahead of LOOKUP .
@chandoo_2 жыл бұрын
Did you watch the full thing? you will be surprised.
@AliensTech2 жыл бұрын
@@chandoo_ I did. I mentioned interms of complexity. You are right with large data collections.
@09336371182 жыл бұрын
Could you give me the data you used please..
@chandoo_2 жыл бұрын
The files are large, so I can't share them. Please make up some random data using RANDARRAY or RANDBETWEEN formulas.