INDEX & MATCH or XLOOKUP for Lookup Formula in SUMIFS Criteria Argument. Excel Magic Trick 1668.
Вставка
- Опубліковано 12 тра 2020
- excelisfun.net/files/EMT1668....
Learn how to lookup up Book ID given a book title, inside the criteria argument of the SUMIFS Function, with the goal of add total units sold for each book. See traditional formula using INDEX, MATCH & SUMIFS. See new Microsoft 365 Spilled Array Formula using XLOOKUP and SUMIFS.
Fundamental Reporting Problem illustrated in Video: We need totals for Book Title, but Data Set Has Book ID for each record, not book title.
Topics:
1. (00:01) Introduction
2. (00:37) INDEX and MATCH Function to “Lookup an Item to the Left”. Goal: Lookup Book ID, so we can use it inside SUMIFS. This is a Traditional Formula.
3. (01:41) SUMIFS to add units for each book ID.
4. (02:12) What we are required to do with a Traditional Excel Formula.
5. (02:20) XLOOKUP to lookup Book ID and Spill all the Book IDs. This Spilled Array Formula does not require that we lock cell references or Copy the Formula.
6. (03:46) Why Spilled Array Formula are MUCH Easier than Traditional Formulas.
7. (03:56) SUMIFS with XLOOKUP to add units for each book ID.
8. (04:28) Summary of Video and End Video Links
Very cool! Xlookup in SUMIFS 👍
Cool, Teammate : )
Hi Mike.. thanks for the old and new Wednesday fun with SUMIFS, INDEX/MATCH and XLOOKUP. So many great and creative ways to use these functions. Thanks for sharing. Thumbs up!!
You are welcome for the Wednesday fun, Old and New, Wyane!!!
This is exactly what I needed for a problem that just came up. Amazing that is was right there on your home page, didn't even need to ask the right question in your search. Thanks Mike!!
I always enjoy learning and I always enjoy your videos because I do learn!
Glad you enjoy and learn, John : ) : )
I really do appreciate all of these videos. You have definitely helped me with my work productivity.
Glad to help with productivity at work, Charging Forward! Love the user name ; )
That is a fun example Mike. Thank you for the video. It is neat to use a lookup formula inside of SUMIFS.
Thank you sir for the video. Always get to learn new things from your videos.
Glad to help you learn new things, Manohar!!
Thank you for including both old and new methods ... It means a lot for me
You are welcome! Old and New: they both are good : )
I like the way you make use of Xlookup, thank you so much Mike
I am loving the new dynamic array formulas. Great use of xlookup. Thanks Mike.
Me too - love spilled arrays! Thanks for stopping by, Matt : ) : )
Brilliant as always! Thank you, sir!
You are welcome for the brilliance of Dynamic Arrays, Shayne!!!
Boom!2 Awesome Formulas..Really Impressed With All The Dynamic Array Functions.Great Stuff Thank You Mike :)
You are Boom-Boom welcome, darryl : )
Lovin' dynamic array functions! Thanks, Mike
You are welcome, Jim!!!!
you are making my work life easier with every new tip, thank you!!!!!
You are welcome, Krishna!!!
You make things simple to understand. Thank you, sir.
You are welcome for the presentation that tries to make the complex, less complex, prakash!!!
I like your old/new school comparisons. It's very useful! Thanks Mike :)
Yes, and we better get used to it, since much of what we did in the past will have a more efficient new way : ) : )
Formula spill is so awesome! Thanks!
Yes, it is pretty amazing, the spill : ) :)
Wonderful as always. Thanks Mike.
Glad it is wonderful for you, Edmundo!!!!
Thanks Mike. WOW X Lookup. Index and Match still powerful. Loved this!!!! : ) : )
It is the Calc Engine which makes the formula amazing, more than XLOOKUP. XLOOKUP only provides alternative to INDEX and MATCH, but the Calc Engine allows us to not lock and not copy : ) Thanks for stopping by, Formula Guy Borg : )
Awesome Mike. XLOOkUP is the best !
Yes, it is : ) : )
Great video. This will be so helpful for work
such a great place to learn excel, u r a cool and super guy. thanks a lot of you sir.
You are an amazing teacher, keep it up boss
Okay, I will keep it up, Farhan!!!!
Easily my new most used function. I'm so glad that my company switched from Office 2010 straight to Office 365.
Excellent video, as usual, Mr. Girvin. I am sending this link out to my current and former accounting students. Thank you.
Awesome! We love accounting students : )
Don't have Excel 365 yet but this is something worthy to learn about. Thank you Mike!
You are welcome, orlando!!!!
Thanks Mike for this fun. 🎉 💐 🎊
You are welcome for the fun! Thanks for the flowers : ) : )
Thanks!!! Learn something new everyday !
You are welcome for the new everyday, mytubeview : )
Excellent video, very useful, you think of all users who have diferent versions of Excel. You are an outstanding teacher
You are welcome, aguerojg!!!! Glad to help all Excel users : )
Thanks a lot!! Very instructive!!
Glad it is instructive, Bruno : ) : )
Xlookup is just amazing .... thanks Mike
You are welcome for the XLOOKUP fun, Hussein!!!
Great video.. you have inspired me to start my own channel and company with the knowledge I've learned here. Thank -You!!
I am glad that you have been inspired, EELT!!!
It's very useful! Thank you
Glad it helps!!!
very nice Mike, Thanks
Thanks for teaching this amazing feature...
You are welcome for the new feature teaching, Kashif!!!
As always great video
Glad it is great for you and thanks for stopping bu, hakim!!!
great as always
Muito legal! Parabéns. Adorei a comparação.
: ) : )
Thanks Mike.👍
Awesome!
Awesome trick
Thanks Mike, that's is really cool.
Glad it is cool for you, Sajid : ) : )
Thanks for sharing 👍
You are welcome for the share, Faisal!!!
Thanks Mike!
Glad you like it, Level Zero!!!
XLOOKUP & SUMIFS for the win!
For the win : ) : ) : ) : )
Excellent video.
Glad it is EXCELlent for you, Ashok!!!
I like it. A virtual helper column.
I've been updating my models with Dynamic Arrays and XLOOKUP and it's saving a lot of steps.
I just have to decide which is best to use where, because often I can reach it either way.
Here is an alternate new school solution:
=SUMIFS(C5:C22,B5:B22,FILTER(I5:I10,J5:J10=E5:E10))
Thanks.
Very Nice, Jason M!!!! Your formula is a precursor to the next video where we run an OR Logical test...
Amazing vedios!!
Your videos and tutorials have really inspired me to create more such content.
Thanks for the inspiration!!
Love it! I Just Subbed to your channel. Cool : )
Thanks a ton 😍👍🏼
@@FinGeeks You are welcome : )
Brilliant
Awesome
Excellent
Glad you like it!!!!!
Love it so much
Glad you love it!!!!
i Really appreciate this video
Glad it helps!!!!
its great sir thank you so much
You are welcome, Mobin!!!
New school method pretty cool :)
Yes, indeed, New School is cool : )
Hi Mike
Hope you are doing fine.
Very big thanks to share another amazing video with us
Very big thnax👍👍👍👌👌👌💐💐💐
I have 1 query regarding to separate text given jumble data e.g. a1s2f3 my output is i need only asf but without use substitute
Please share.
Thanks in advance
Regards,
Ashish
Maybe if item in A2:
=CONCAT(FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISERROR(--MID(A2,SEQUENCE(LEN(A2)),1))))
@@excelisfun thanx👍👍👍💐
Gratitude!
Glad you like it, Ankur!!!
Nice
Glad it is nice for you, Phone Excel : )
Thumbs up!
Thanks, Teammate : ) : )
Love your videos! Quick Question- would Xlookup return an error if someone opens the file with an older version of Office (2016 or non- office 365)?
Not at first, but once you put cell in edit mode you will get error. It is not backward campatable.
very nice 👍👍👍👍
Glad it is nice for you,vijay!!!!
Fantastic stuff as always! Can you create a video showing how to nest the indirect, xlookup and sumifs in the same formula? Perhaps looking across multiple tabs? Thanks for all of your great work!
Glad it is fantastic for you, Daniel. What is the problem that you are trying to solve with INDIRECT, SUMIFS and XLOOKUP?
@@excelisfun Thanks for asking the question. I was just wondering if the same process of nesting a xlookup inside of a sumifs formula could leverage the indirect formula (or any other formula) to do the same process across multiple worksheets. Thanks.
@@danielmain3975 Yes, it could. Do you have different lookup tables on different sheets, or what is the set up?
Exactly! Similarly structured lookup tables throughout a workbook.
If there are two or more dublicate values at same range for looking up, what should we do for calculation?
It depends on what you want to do with dups. Lookup functions only get first one. FILTER gets all matching values.
Hi sir thanks for all the awesome video that you had done It help me alots. May I know how to use excel for interpolation? Is that any video I can learn from?
I am sorry, I am not sure how to do interpolation : (
Awesome....on quick question: if hypothetically in the column F there are some duplicates how can we fix it?
I am sorry, I do not understand what you are asking.
Columns F and G are SUMIFS columns so potentially every cell could have the same value as are two in the example.
Could we find multiple positive number against multiple negative numbers and total comes up to zero...
What if the book title "Good Data" wasn't just = to GD14 but also GD15, and GD16, (hard back, digital i guess?) and we wanted to sum units sold for all "Good Data" books sold with multiple book id's?
Hi Sir how to select 3 large numbers above specific number in another column, like I have numbers in column 1-10,20,30,40,50,60,70,..,100 and in column 2-101,105,104,99,..200.
I want to extract 3 large numbers from column 2 which are greater than 50 in column 1.
Please help me to solve the problem
Let's say we use vlookup formula all the way down to 70k rows , and it makes excel file heavy. Does spilling formula also increase file size and calculation time?
If you have spilling formulas in your version of Excel, then you have the new calculation engine which does VLOOKUP and other lookup formulas MUCH faster than in he past. You should be fine with the new Excel on 70 k rows.
@@excelisfun thank you!
Cant nearly follow u pace.....have to see it again and again.........👍 thx for u great shared skills.👍
You can use the gear icon in the lower right below the video to change the speed of the video ; )
ExcelIsFun thx, yes i can. 👍 It was fun, i meant u r so good an fast explaning, i nearly cant follow 😀😉
"That IS amazing!"
"It IS!!!!!!" The new Excel Engine and Spilled Arrays are so helpful!!! Thanks for stopping by in the comments for videos that are not MSPTDA : )
and what happens when you send a "New school sheet" with xlookup to a person that dosen't have 365?
You see the answers but can not edit for formula. If they put it in edit mode, the formula gives an error. Here is a video about this issue: ua-cam.com/video/nkXh5OFKeXg/v-deo.html
Thanks for sharing! It’s great to see the different lookups in sumifs. 🙂 after watching many tutorials I thought I’d give it a go too, would be great to get some feedback on my channel? 😅
Xlook up Function si just for excel 365?
Yes, XLOOKUP and the Spilled Arrays are only in 365.
Is XLOOKUP available in MS 2016 or 2019?
Unfortunately not. Office 365 exclusive alongside with a bunch of a new functions.
With this formula you also don't have to copy =SUMIFS($C$5:$C$22;$B$5:$B$22;INDEX(I5:I10;MATCH(E5:E10;J5:J10;0)))
Yes! Very cool, 67duiker! But you do need Microsoft 365 Excel ; )
Brilliant!
Thank You for Sharing!
hi I have big email list , some emails contain .com, some .com.au, some contain (com.) and some contain (.com.au.) ,,,,, i need to erase the (dots) in the last two examples, could you help
The "best" school: power pivot relational tables
School is cool : )
Nice
Glad you like it, Way2Excel!