ONLY Excel pros use this INDEX MATCH trick 💡
Vložit
- čas přidán 15. 05. 2023
- This INDEX MATCH trick that will surely blow your socks off. And then it blows your pants off too with an even better XLOOKUP variation. So yeah, bring some spare clothing and watch 😎
📂 Download the sample file:
chandoo.org/wp/wp-content/upl...
💡 Learn more:
on XLOOKUP (500k views) - • I don't use VLOOKUP an...
on INDEX MATCH (100k views) - • Excel's INDEX + MATCH ...
on VLOOKUP (full intro + examples) - • Excel VLOOKUP - Compl...
VLOOKUP or INDEX MATCH (interview questions) - • VLOOKUP or INDEX+MATCH...
on FILTER (400k views) - • I don’t use filters in...
~
What is the data analyst still single?
Because he is waiting for the EXACT MATCH... 🤣
#excel - Věda a technologie
Cool, or FILTER, deals also with multiple matches, not only single ones. ✌
True, ive used filter and xmatch together and it is awesome.
Why to use xmatch just use filters on all the columns acc to ur need i think that should also work
I had been on this for hours!!!! Went through different videos on CZcams. Yours was the only one that gave me what I was looking for, the right result. THANK YOUUUUUU!
Wow.
I am still novice to excel but this is very cool.
I have only been on my excel journey about 8 months. Have so much to learn😊 I am hype about learning it though.😃
Truly mind blowing! Thank you Chandoo for sharing this! 🙂
Every time I start thinking I'm getting pretty good at Excel, a new Chandoo video comes out and reminds me how much I have yet to learn.
Chandoo you are a life-saver.
I was so bad at excel before I know ur channel, know I'm getting better :D
I learned this couple of days back and such a cool hack
I was looking for the same formula just a week ago. Thanks for making it simple.
This is excellent and exactly what I was looking for for some time
Wow. Just wow. This is so smart!
this is awesome! Thank you
Espectacular !!
Excellent Chandoo!
Awesome trick thank you very much 👍
Thank you Very much for this video i tried the same for my daily company work and it worked for me..
Great. Thanks for sharing
Hi Chandoo, an awesome trick using index match. If I were given this problem, I would have probably used the Sumifs function
Ditto. I have done similar things earlier but with sumifs
Thanks RR :)
Yes, SUMIFS is a great alternative in this case. The advantage of INDEX / MATCH or XLOOKUP or FILTER is that they work even when you want non-numeric columns or need to get multiple columns of output.
Chandoo Awesome as ever 🏅
Super Chandoo anna, learnt a new formula today.
I always confused how to use index match if the both reference is row and after watching countless Excel video i finally found this
Thank you sir, thank you very much
nice...thank you
Thank you bro for shearing
This is mind-blowing
Fantastic
Sumif may also work
Is it possible to build on or modify the XLOOKUP version to sum multiple true matches?
It would be cool to be able to replace SUMIFS in that scenario.
Awesome trick
We can also perform the same using vlookup, but concatenating 3 columns
amazing
Well, do you know how to use index match without cse for multiple criteria? If not, i can share after i check it in excel.
A simple SUMIF solve problems like this!
Hi Chandoo that was awesome. But did it by Sumifs and got the results. Yes but only when output is in number not text.
Dear Chandoo can we use the same formula to get data from one table to another table . I was trying to so formula gives error when one column value repeats in one column while other is different in both
Hi Anna
Can you suggest me one format stock vs demand ki report ela cheyalo stock enti ante FG goods packing material
Yeah Great one!, but we can do this with Sumifs too If I'm not wrong?
Is it not better to use: =SUMIFS($F$10:$F$369,$C$10:$C$369,D3,$D$10:$D$369,D4,$E$10:$E$369,D5), then this will take into account any multiple entries for the same month and also eliminate the need to use an array?
That’s what I thought!!
Yes. It'll also be easier for good-but-less-than-expert colleagues to be able to audit your formulas too (or keep them from screwing it up when they _try_ to audit, but don't know about Ctrl+Shift+Enter). Really no reason to hack a formula when a built-in one does the exact same thing.
Chandoo, first off, LOVE your content. I have a question regarding the lookup...
I have lists of employees that may move from one list to the other (there are five lists for example)...I keep a current list, then a departures list for each, and all is combined (appended) using power query into a table called EE_DATA. If I need to find an employee using an Xlookup, it will only find the first result in EEDATA, which could be wrong. How do I use the XLOOKUP to find the latest result?
XLOOKUP has an optional search_mode parameter that when set to -1, does a search in reverse order
If multiple header we can use same trick ?????
How can you do Index Match with criteria in a column and row?
What is the 1 at the first place in the formula?
Hi Chandoo great using your old trick instead of CSE can this be the other alternative INDEX(F10:F369,MATCH(1,((INDEX(C10:C369,)=D3)*(INDEX(D10:D369,)=D4)*(INDEX(E10:E369,)=D5)),0)). 🙏
Clever
sir, why is 1 used in the match function @chandoo
The mathematical way to represent "AND" condition.
Hi Chandoo,
This is nice.
But did you know that you can achieve a similar result (when searching for Numeric values only) using SUMPRODUCT, without having to use an array formula.
=SUMPRODUCT(F10:F369*--(C10:C369=D3)*--(D10:D369=D4)*--(E10:E369=D5))
hey, but that only works if column F are numbers. :)
Sumifs is even easier.
How to reconcile Purchase Register with GST Portal data. As different persons account invoice numbers differently though the remaining values match with GST Portal data, we cannot get the required results such as MATCHED or PARTIALLY MATCHED etc
Hello Chandoo, hope you are well. I have a small work on excel which I will like you to do. Do you have a website that I can reach out for your contact?
I have a similar task, but I have 1 cell with hundredds of words, basically it's 1 large paragraph that I'm looking to go into and change multiple partial strings. I am using a two column approach, column 1 is the word I'm looking to replace, while column 2 is the new replacement word, but I can't seem to find the correct formula to replace multiple values all at once :( , if anyone can help I would greatly appreciate it !!
sir , sumproduct( (c10:c369=D3)*(d10:d369=d4)*(e10:e369=d5)*f10:f369)
is any disadvantages with this simple method
Thank you sir, i saw your one of reply i.e index match example works with non numeric values also. thank you very much
Hi Chandoo,
I think using "xlookup" with "&" function will be a better/simpler option.
=xlookup(D3&D4&D5,C10:C369&D10:D369&E10:E369,F10:F369)
Hope you make a video on this too :) Your videos do help me (and all those who follow you :) )
Yes, I have thought of this also, with INDEX, MATCH, same logic of concatenation
=INDEX(F10:F369,MATCH(D3&D4&D5,C10:C369&D10:D369&E10:E369,0))
It is not always the "Correct" option. Imagine you have values like
Sam, Altman Consulting, 23
and
SamAltman, Consulting, 23
Both of them would have the same concatenated values. So the MATCH / XLOOKUP would pick up the wrong results.
Thanks chandu ji, 1question what if there is iteration/ duplication in records and we need as sum in result
In that case, you can use SUMIFS...
Why cant i use Sumifs here?
You can.. but this will work even when you want to return a non-number column.
What is match 1 in the formula and how many conditions we can add
You can add any number of conditions.
Our conditions multiplication results in a bunch of 1s & 0s - 1 where all the conditions are met and 0 where at least one condition failed. As we want to match the row that met all the rules, we need to look for 1. Hence match 1.
👌
Excellent tip. But what if two records meet the criteria?
If you range-name the respective columns: =FILTER(Budget,(Person=D3)*(Country=D4)*(Month=D5))
This is an awesome trick, but it's quite old. Nonetheless, I'm sure it's a mind blower for many
Sheet 1, A column have a to z values, fetch/paste a to z each letter in each sheets at fixed cell. Example....
Sheet2 D4 cell need letter a,
Sheet3 D4 cell need letter b
Sheet4 D4 cell need letter c
Please teach me how to do????
I knew it, yet SUMPRODUCT does the same thing
SUMPRODUCT is awesome 😎
But with newer functions like FILTER & Dynamic Array behaviour in Excel, I almost stopped using SUMPRODUCT.
But why to make things fancy and complicated when you can use simple SUMIFS formula in this scenario
When python Pandas is going to release from our channel
I only use Python for fun and not doing any real work. Most of what I do is in Excel / Power BI and SQL. So those are the topics I will be covering for a while. I do have 2 videos on Python here - czcams.com/play/PLmejDGrsgFyCRceKns-9snhrIKR0d9XMm.html
WASTE OF TIME
Hi chandoo what's the 1 in xlookup formula
Hi Chandoo, I hope you are fine 😀
My name is Chris and I really like your video editing style 😍 i follow you on CZcams and as a french Excel content creator and I would like to know if you would accept to discuss with me with to get advice from your about that.
If so, i will adapt my availability your schedule for sure.
If you are ok I will send you my email. Have a great Day Chandoo 🙂