3-Way Lookup Adding: XLOOKUP or INDEX inside SUMIFS? -- Excel Magic Trick 1602
HTML-код
- Опубликовано: 8 сен 2024
- Download Excel File (to follow along with video): excelisfun.net...
Learn about how to use three lookup values to lookup values and then add. The trick is that two of the lookup values are in the records and one is in the Column Headers. See how to lookup a column with XLOOKUP or INDEX and MATCH functions, then but the resultant range object into the SUMIFS Function.
02:47 Bonus Formula #1 03:16 Bonus Formula #2
Lots of fun with this one. Many ways to do it, but my guess is that the SUMIFS will be fastest:
=SUMIFS(XLOOKUP(C12,$C$1:$E$1,$C$2:$E$9),$A$2:$A$9,A12,$B$2:$B$9,B12)
=SUMIFS(INDEX($C$2:$E$9,,MATCH(C12,$C$1:$E$1,0)),$A$2:$A$9,A12,$B$2:$B$9,B12)
=SUMPRODUCT(INDEX($C$2:$E$9,,MATCH(C12,$C$1:$E$1,0)),--($A$2:$A$9&$B$2:$B$9=A12&B12))
=SUM(FILTER(XLOOKUP(C12,$C$1:$E$1,$C$2:$E$9),($A$2:$A$9=A12)*($B$2:$B$9=B12)))
=SUMPRODUCT($C$2:$E$9*($C$1:$E$1=C12)*($A$2:$A$9=A12)*($B$2:$B$9=B12))
=SUM(FILTER(FILTER($C$2:$E$9,$C$1:$E$1=C12),($A$2:$A$9=A12)*($B$2:$B$9=B12)))
Great MIKE always give new thing in excel
Glad you like the new things, Amit!!!
Great video that shows how the supercharged XLOOKUP can in turn supercharge other Excel functions. SUMIFFS is only one of many functions that benefit from XL and the "new"calc engine.
Yes, only one of many : ) : ) : )
just one word: AWESOME! thank you very much . your explanations are very clear. all the best.
Glad it is clear for you, elhassan!!!
Xlookup is very Amazing, Thanks Mr. Mike.
You are welcome Stat Guy Ogwal!!!!
Your the man thank you for all you do.
You are welcome, Ricardo!!!!
Another great video on XLOOKUP! Good job Mike
Thanks, Chris!
And my fvourite lookup adding - so easy! Excel Is Magic!Thanks for sharing :)
You are welcome for the share, Malina : )
Hi Mike,
Thanks for another great video.
The bonus 2 was fun.
You are welcome for another, fun one with bonus, Khalid!!!
Had to come back to this video today and utilize it. Thanks Mike!
thank you!! this was really helpful!
Glad it was helpful!
Hi Mike.. great video.. love when you show the various different ways to get to the same result and also the good explanation of delivering a range object to the various IFS functions with XLOOKUP and INDEX. Thumbs up!
Thanks, Wayne!!!!
Hi Mike, can you please teach us how to do it in power query.
Thanks in advance
Wonderful video mike; I like INDEX & MATCH combo with SUMIFS :)
Cool! Glad you like it, Sachin!!! I like it too : )
So many lookup options. Thanks for the great lookup video Mike.
You are welcome, N Sanch01!!!
thanks you
You are welcome!!
Excel Master.Amazing video's channel .
Thanks Mike. Amazing Staff!!! It is recommended that to learn the 'Nuts and Bolts' of the XLookup, the videos should be downloaded to study. :) :)
Yes, nuts are bolts are fun!!!!!
well explained!
Glad it helps, Steven Nye!!!!
Thanks Amazing Mike for this EXCELlent video.
Glad to provide EXCELlent fun, Syed : ) : )
Love method 1
Thank you Mike, a really useful...as always!
You are welcome, Paul!
Great as always!
Thanks, Teammate pmsocho : )
Nice example of using the new XLOOKUP!
For the SUMPRODUCT solution, we may simplify it a bit, without using INDEX, MATCH.
=SUMPRODUCT($C$2:$E$9,($A$2:$A$9=$A12)*($B$2:$B$9=$B12)*($C$1:$E$1=$C12))
Yes, it may simplify it, but it will take MUCH longer to calculate on a large data set than using SUMIFS with either XLOOKUP or INDEX. In the book I wrote, Ctrl + Shift + Enter, Mastering Excel Array Formulas, I timed and compared many Array Formulas and the consistent pattern emerged that Direct Array Operations almost always take longer to process than Built in Functions. The Direct Array to pick out the column: $C$1:$E$1=$C12 will take longer than the built in functions to lookup a row like INDEX and XLOOKUP. Also, the Direct Array Operations for row conditions, $A$2:$A$9=$A12 and $B$2:$B$9=$B1 will take longer than calculating the conditions in SUMIFS. As I discuss in my book, and in 100s of videos I have posted, you will not notice any performance slow down on a small data set or a small workbook with only a few formulas to calculate, but when you get larger data sets or large models with many formulas, you will feel the slowness. The reason for all this is fairly simple: when making direct array operation, Excel has to calculate an answer for each cell in all the ranges, but with built in functions, they rely on the program code which is almost always faster than doing the calculation cell by cell. An Interesting note: I have not done much timing of formulas since the new calculation engine came out, but Excel MVP Charles Williams has done a lot of timing and he says that the new engine is faster in many ways. Even with that, my guess is that cell by cell calcs will be slower.
ExcelIsFun thank so much for the detailed explanation. 👍🏻
@@wmfexcel Go Team!!!!
Thanx
Sir, i have been a fan of all your videos and always refer to your videos when I need any help with excel. I am an accountant and struggling to look up the function that will help me look up the one criteria in multiple sheets and add the value from all those sheets into my index page. Is there a simple way of doing this ? The Xlookup and vlookup function is great when I am just dealing with one sheet.
Hey, Have you uploaded video related to different uses of Xmatch particularly and with xlookup?
And i want to know the alternate to Sumifs..is it possible?
again , other option could be FILTER like in =SUM(FILTER(FILTER($C$2:$E$9,$C$1:$E$1=C12),($A$2:$A$9=A12)*($B$2:$B$9=B12))) . Interesting to know , like Mike always did regarding timing , which one of various methods can run faster for large data sets.
Well, it would be interesting to time, but almost always, direct array operations (like ($A$2:$A$9=A12)*($B$2:$B$9=B12))) will be slower than built in functions like INDEX or XLOOKUP (to lookup row) or SUMIFS. I wrote the book Ctrl + Shift + Enter, Mastering Excel Array Formulas, and in that book I did a lot of timing, and that was the general conclusion. But, although that is the rule that holds most of the time, you always have to time to make sure : )
LOVE your inventive formula, cr gr0912!!!!
@@excelisfun I know your CSE course, your CSE course should be the holly grail of excel learning, even with the new help of dynamic arrays stuff , people should know how the inner organs of excel are functioning . I am positive that the best VBA programmer is the one who knows how to do it also without VBA, that's the challenge, to understand the guts of excel, something that you do at the best.
Hello Sir,
A B C
1.20 40 30
2.(10) 50 [60
3. 20 30 20
4. 50 10 10
5. 60 10 80
6. 30 (90) 50]
7. 20 20 70
In above eg. A2 has minimum value & B6 has maximum value, so from this range
2-6 I want sum of column C2-C6 (60+20+10+80+50).
so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C.
I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner.
I m providing one more eg. so that u have better understanding.
A B C
1. 50 30 30
2. 60 20 40
3. (30) 10 [50
4. 90 80 60
5. 70 (90) 30]
6. 50 70 20
7. 40 40 10
from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30].
Please help with this question.
Thank You.
How would you do this if you wanted only 2 criterias? Meaning you looked up california and then product? When I do it this way like you are it gives me a value? Would it just be a sumif and not sumifs?
Good video, Mike. How are we users to know that INDEX and XLOOXUP deliver a range object rather than an array object? MS documentations doesn't mention it.
We don't. Microsoft doesn't ever tell us. And is not directly documented. We just have inferred it since SUMIFS and the like are programmed to only accept ranges and what is delivered by INDEX and OFFSET and now XLOOKUP are accepted by SUMIFS and the like. At least that is what I wrote in my book after lots of tests and form discussion over the years.
@@excelisfun How monolithic of them. I can't begin to describe the toils I've had creating complex formulas with what should obviously be MAXIFS, MINIFS SUMIFS, only to get error messages. I usually just give up on them and multiply arrays inside SUMPRODUCT to avoid the painful troubleshooting. Thanks tons for you awesome insight, Mike!!!
@@drsteele4749 SUMPRODUCT is very handy and has rescued us for decades, luckily : )