ONLY Excel pros use this INDEX MATCH trick 💡

Подписаться 631 тыс.

Просмотров 40 тыс.

50% 1

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

Наука

Опубликовано:

15 май 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 82

@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 9 месяцев назад

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

@IarukaSkYouk Год назад

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

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

@GameAGuy 5 месяцев назад

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

@vijayarjunwadkar Год назад

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

@pokerhearts Год назад

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

@mohsinahmed5678 Год назад

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

@soujanyapilli1992 Год назад

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

@sandorszilagyi5929 Год назад

Awesome trick thank you very much 👍

@AnilSumanam 24 дня назад

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

@tisay103 4 месяца назад

this is awesome! Thank you

@deinbleiz 10 месяцев назад

Wow. Just wow. This is so smart!

@chrism9037 Год назад

Excellent Chandoo!

@ICTNuggets Год назад

Great. Thanks for sharing

@blitzkrieg0136 9 месяцев назад

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

@Fxingenieria Год назад

Espectacular !!

@peterluxford6752 Год назад

Chandoo Awesome as ever 🏅

@ysantosh Год назад

Super Chandoo anna, learnt a new formula today.

@CideeTV Год назад

nice...thank you

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

@messaoudpolitique9208 Год назад

Thank you bro for shearing

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

@santhoshsubbiah1803 Год назад

This is mind-blowing

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

@balajimudekulam8135 Год назад

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

@yousrymaarouf2931 Год назад

Fantastic

@mateotinoco2393 Год назад

Awesome trick

@sanket565 Год назад

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

@ananda141987 5 месяцев назад

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

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

@LearnValue Год назад

Sumif may also work

@Naresh62648 Год назад

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

@mgonyea01 Год назад

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

@danishnawaz3651 Год назад

amazing

@thojeda Год назад

A simple SUMIF solve problems like this!

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

@SamehRSameh 7 месяцев назад

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

@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

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

@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

@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 7 месяцев назад

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.

@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

@user-hl7ej9sc7z 5 месяцев назад

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?

@rajatverma6042 Год назад

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

@Hadarel87 Год назад

Clever

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

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

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

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

@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 7 месяцев назад

Sumifs is even easier.

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

@ishaikhi Год назад

Why cant i use Sumifs here?

@chandoo_ Год назад

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

@IssueBoyStefan Год назад

The mathematical way to represent "AND" condition.

@babateknicalgee1839 Год назад

👌

@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

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

@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 - ru-vid.com/group/PLmejDGrsgFyCRceKns-9snhrIKR0d9XMm

@tommyharris5817 11 месяцев назад

WASTE OF TIME

@kamilahmed6539 Год назад

Hi chandoo what's the 1 in xlookup formula

@excelbooster3327 Год назад

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