ONLY Excel pros use this INDEX MATCH trick 💡

[@Excelambda]

Cool, or FILTER, deals also with multiple matches, not only single ones. ✌

[@julesmansour3415]

True, ive used filter and xmatch together and it is awesome.

[@nikunjgorani8964]

Why to use xmatch just use filters on all the columns acc to ur need i think that should also work

[@ufoufo9182]

I had been on this for hours!!!! Went through different videos on KZbin. Yours was the only one that gave me what I was looking for, the right result. THANK YOUUUUUU!

[@soujanyapilli1992]

I learned this couple of days back and such a cool hack

[@IarukaSkYouk]

Chandoo you are a life-saver. I was so bad at excel before I know ur channel, know I'm getting better :D

[@vijayarjunwadkar]

Truly mind blowing! Thank you Chandoo for sharing this! 🙂

[@michaelt312]

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.

[@mohsinahmed5678]

This is excellent and exactly what I was looking for for some time

[@chrism9037]

Excellent Chandoo!

[@rangerover5635]

Hi Chandoo, an awesome trick using index match. If I were given this problem, I would have probably used the Sumifs function

[@trulyani]

Ditto. I have done similar things earlier but with sumifs

[@chandoo_]

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.

[@thojeda]

A simple SUMIF solve problems like this!

[@tisay103]

this is awesome! Thank you

[@deinbleiz]

Wow. Just wow. This is so smart!

[@pokerhearts]

I was looking for the same formula just a week ago. Thanks for making it simple.

[@GameAGuy]

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.😃

[@ysantosh]

Super Chandoo anna, learnt a new formula today.

[@AnilSumanam]

Thank you Very much for this video i tried the same for my daily company work and it worked for me..

[@peterluxford6752]

Chandoo Awesome as ever 🏅

[@geoffwatson]

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?

[@thojeda]

That’s what I thought!!

[@bryan__m]

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.

[@ICTNuggets]

Great. Thanks for sharing

[@LearnValue]

Sumif may also work

[@messaoudpolitique9208]

Thank you bro for shearing

[@sandorszilagyi5929]

Awesome trick thank you very much 👍

[@Fxingenieria]

Espectacular !!

[@sanket565]

We can also perform the same using vlookup, but concatenating 3 columns

[@santhoshsubbiah1803]

This is mind-blowing

[@blitzkrieg0136]

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

[@mateotinoco2393]

Awesome trick

[@CideeStudio]

nice...thank you

[@ishaikhi]

Why cant i use Sumifs here?

[@chandoo_]

You can.. but this will work even when you want to return a non-number column.

[@ananda141987]

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

[@vipul4raf]

Hi Chandoo that was awesome. But did it by Sumifs and got the results. Yes but only when output is in number not text.

[@SamehRSameh]

If multiple header we can use same trick ?????

[@yousrymaarouf2931]

Fantastic

[@papettipaolo]

What is the 1 at the first place in the formula?

[@JAEXCEL]

sir , sumproduct( (c10:c369=D3)*(d10:d369=d4)*(e10:e369=d5)*f10:f369) is any disadvantages with this simple method

[@JAEXCEL]

Thank you sir, i saw your one of reply i.e index match example works with non numeric values also. thank you very much

[@DanhNyiahm]

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?

[@balajimudekulam8135]

Yeah Great one!, but we can do this with Sumifs too If I'm not wrong?

[@rajatverma6042]

sir, why is 1 used in the match function @chandoo

[@Metzanine]

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.

[@aart_analyst]

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))

[@lulouise6790]

hey, but that only works if column F are numbers. :)

[@bryan__m]

Sumifs is even easier.

[@danishnawaz3651]

amazing

[@Sekoleyte]

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.

[@Naresh62648]

Hi Anna Can you suggest me one format stock vs demand ki report ela cheyalo stock enti ante FG goods packing material

[@wordlustjoshi1261]

What is match 1 in the formula and how many conditions we can add

[@chandoo_]

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.

[@mgonyea01]

How can you do Index Match with criteria in a column and row?

[@JasonSandeman]

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?

[@aart_analyst]

XLOOKUP has an optional search_mode parameter that when set to -1, does a search in reverse order

[@suryasabniveesu6946]

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

[@abbottkatz8830]

Excellent tip. But what if two records meet the criteria?

[@abbottkatz8830]

If you range-name the respective columns: =FILTER(Budget,(Person=D3)*(Country=D4)*(Month=D5))

[@IssueBoyStefan]

The mathematical way to represent "AND" condition.

[@bharathramc.n7796]

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)). 🙏

[@canirmalchoudhary8173]

I knew it, yet SUMPRODUCT does the same thing

[@chandoo_]

SUMPRODUCT is awesome 😎 But with newer functions like FILTER & Dynamic Array behaviour in Excel, I almost stopped using SUMPRODUCT.

[@MrKathayat]

Thanks chandu ji, 1question what if there is iteration/ duplication in records and we need as sum in result

[@chandoo_]

In that case, you can use SUMIFS...

[@ganeshbhujbal7440]

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 :) )

[@gabrielgordon]

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))

[@chandoo_]

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.

[@obscene187]

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 !!

[@jahabaralinoormohamed6625]

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????

[@karthikkayan8049]

Why use 1 match formula after

[@Hadarel87]

Clever

[@MohammedKhan-rz1gz]

This is an awesome trick, but it's quite old. Nonetheless, I'm sure it's a mind blower for many

[@ImranShaikh_111]

But why to make things fancy and complicated when you can use simple SUMIFS formula in this scenario

[@kamilahmed6539]

Hi chandoo what's the 1 in xlookup formula

[@babateknicalgee1839]

👌

[@dasthagirimunna7017]

When python Pandas is going to release from our channel

[@chandoo_]

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 - kzbin.info/aero/PLmejDGrsgFyCRceKns-9snhrIKR0d9XMm

[@tommyharris5817]

WASTE OF TIME

[@excelbooster3327]

Hi Chandoo, I hope you are fine 😀 My name is Chris and I really like your video editing style 😍 i follow you on KZbin 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 🙂