INDEX & MATCH or XLOOKUP for Lookup Formula in SUMIFS Criteria Argument. Excel Magic Trick 1668.

Рет қаралды 28,036

4 жыл бұрын

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

Пікірлер: 159

@LeilaGharani 4 жыл бұрын

Very cool! Xlookup in SUMIFS 👍

@excelisfun 4 жыл бұрын

Cool, Teammate : )

@wayneedmondson1065 4 жыл бұрын

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

@excelisfun 4 жыл бұрын

You are welcome for the Wednesday fun, Old and New, Wyane!!!

@dutchcadillac3977 4 жыл бұрын

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

@JohnOConnor1963 4 жыл бұрын

I always enjoy learning and I always enjoy your videos because I do learn!

@excelisfun 4 жыл бұрын

Glad you enjoy and learn, John : ) : )

@ChargingForward 4 жыл бұрын

I really do appreciate all of these videos. You have definitely helped me with my work productivity.

@excelisfun 4 жыл бұрын

Glad to help with productivity at work, Charging Forward! Love the user name ; )

@manoharchaudhary6505 4 жыл бұрын

Thank you sir for the video. Always get to learn new things from your videos.

@excelisfun 4 жыл бұрын

Glad to help you learn new things, Manohar!!

@samsami5923 4 жыл бұрын

Thank you for including both old and new methods ... It means a lot for me

@excelisfun 4 жыл бұрын

You are welcome! Old and New: they both are good : )

@nsanch0181 4 жыл бұрын

That is a fun example Mike. Thank you for the video. It is neat to use a lookup formula inside of SUMIFS.

@chrism9037 4 жыл бұрын

Awesome Mike. XLOOkUP is the best !

@excelisfun 4 жыл бұрын

Yes, it is : ) : )

@mohamedmeshref 6 күн бұрын

♨♨♨Magic way in simplifying the difficult🌺🌼🌷

@excelisfun 5 күн бұрын

Glad to help!!!

@darrylmorgan 4 жыл бұрын

Boom!2 Awesome Formulas..Really Impressed With All The Dynamic Array Functions.Great Stuff Thank You Mike :)

@excelisfun 4 жыл бұрын

You are Boom-Boom welcome, darryl : )

@shayneramirez3745 4 жыл бұрын

Brilliant as always! Thank you, sir!

@excelisfun 4 жыл бұрын

You are welcome for the brilliance of Dynamic Arrays, Shayne!!!

@001prk 4 жыл бұрын

You make things simple to understand. Thank you, sir.

@excelisfun 4 жыл бұрын

You are welcome for the presentation that tries to make the complex, less complex, prakash!!!

@ianodonoghue6011 2 жыл бұрын

Great video. This will be so helpful for work

@mattschoular8844 4 жыл бұрын

I am loving the new dynamic array formulas. Great use of xlookup. Thanks Mike.

@excelisfun 4 жыл бұрын

Me too - love spilled arrays! Thanks for stopping by, Matt : ) : )

@ogwalfrancis 4 жыл бұрын

I like the way you make use of Xlookup, thank you so much Mike

@freakris30 4 жыл бұрын

you are making my work life easier with every new tip, thank you!!!!!

@excelisfun 4 жыл бұрын

You are welcome, Krishna!!!

@jimfitch 4 жыл бұрын

Lovin' dynamic array functions! Thanks, Mike

@excelisfun 4 жыл бұрын

You are welcome, Jim!!!!

@farhanjawed3365 4 жыл бұрын

You are an amazing teacher, keep it up boss

@excelisfun 4 жыл бұрын

Okay, I will keep it up, Farhan!!!!

@mueez89 4 жыл бұрын

Formula spill is so awesome! Thanks!

@excelisfun 4 жыл бұрын

Yes, it is pretty amazing, the spill : ) :)

@StoryBookJunction 4 жыл бұрын

such a great place to learn excel, u r a cool and super guy. thanks a lot of you sir.

@MalinaC 4 жыл бұрын

I like your old/new school comparisons. It's very useful! Thanks Mike :)

@excelisfun 4 жыл бұрын

Yes, and we better get used to it, since much of what we did in the past will have a more efficient new way : ) : )

@edge5817 4 жыл бұрын

Wonderful as always. Thanks Mike.

@excelisfun 4 жыл бұрын

Glad it is wonderful for you, Edmundo!!!!

@msweeney70 4 жыл бұрын

Excellent video, as usual, Mr. Girvin. I am sending this link out to my current and former accounting students. Thank you.

@excelisfun 4 жыл бұрын

Awesome! We love accounting students : )

@aguerojg 4 жыл бұрын

Excellent video, very useful, you think of all users who have diferent versions of Excel. You are an outstanding teacher

@excelisfun 4 жыл бұрын

You are welcome, aguerojg!!!! Glad to help all Excel users : )

@johnborg5419 4 жыл бұрын

Thanks Mike. WOW X Lookup. Index and Match still powerful. Loved this!!!! : ) : )

@excelisfun 4 жыл бұрын

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

@ohdjrp4 4 жыл бұрын

Don't have Excel 365 yet but this is something worthy to learn about. Thank you Mike!

@excelisfun 4 жыл бұрын

You are welcome, orlando!!!!

@sasavienne 4 жыл бұрын

Thanks Mike for this fun. 🎉 💐 🎊

@excelisfun 4 жыл бұрын

You are welcome for the fun! Thanks for the flowers : ) : )

@721MrG 4 жыл бұрын

Easily my new most used function. I'm so glad that my company switched from Office 2010 straight to Office 365.

@sevagbarsoumian516 3 жыл бұрын

very nice Mike, Thanks

@GeorgeAJululian Жыл бұрын

It's very useful! Thank you

@excelisfun Жыл бұрын

Glad it helps!!!

@mytubeview 4 жыл бұрын

Thanks!!! Learn something new everyday !

@excelisfun 4 жыл бұрын

You are welcome for the new everyday, mytubeview : )

@mohan8887 3 жыл бұрын

Awesome trick

@Brushoi 4 жыл бұрын

Thanks a lot!! Very instructive!!

@excelisfun 4 жыл бұрын

Glad it is instructive, Bruno : ) : )

@hakimhakimsoon9674 4 жыл бұрын

As always great video

@excelisfun 4 жыл бұрын

Glad it is great for you and thanks for stopping bu, hakim!!!

@kashmuneer 4 жыл бұрын

Thanks for teaching this amazing feature...

@excelisfun 4 жыл бұрын

You are welcome for the new feature teaching, Kashif!!!

@excelemployeeleavetracker1274 4 жыл бұрын

Great video.. you have inspired me to start my own channel and company with the knowledge I've learned here. Thank -You!!

@excelisfun 4 жыл бұрын

I am glad that you have been inspired, EELT!!!

@tanveerabbas3271 11 ай бұрын

great as always

@HusseinKorish 4 жыл бұрын

Xlookup is just amazing .... thanks Mike

@excelisfun 4 жыл бұрын

You are welcome for the XLOOKUP fun, Hussein!!!

@paulorafaellima7809 4 жыл бұрын

Muito legal! Parabéns. Adorei a comparação.

@excelisfun 4 жыл бұрын

: ) : )

@ashoksahu9546 4 жыл бұрын

Excellent video.

@excelisfun 4 жыл бұрын

Glad it is EXCELlent for you, Ashok!!!

@faisalag9611 4 жыл бұрын

Thanks for sharing 👍

@excelisfun 4 жыл бұрын

You are welcome for the share, Faisal!!!

@Luciano_mp 4 жыл бұрын

Thanks Mike.👍

@simfinso858 4 жыл бұрын

Nice

@excelisfun 4 жыл бұрын

Glad it is nice for you, Phone Excel : )

@jasonm8098 4 жыл бұрын

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.

@excelisfun 4 жыл бұрын

Very Nice, Jason M!!!! Your formula is a precursor to the next video where we run an OR Logical test...

@DougHExcel 4 жыл бұрын

XLOOKUP & SUMIFS for the win!

@excelisfun 4 жыл бұрын

For the win : ) : ) : ) : )

@FinGeeks 4 жыл бұрын

Amazing vedios!! Your videos and tutorials have really inspired me to create more such content. Thanks for the inspiration!!

@excelisfun 4 жыл бұрын

Love it! I Just Subbed to your channel. Cool : )

@FinGeeks 4 жыл бұрын

Thanks a ton 😍👍🏼

@excelisfun 4 жыл бұрын

@@FinGeeks You are welcome : )

@vidalortiz3677 5 ай бұрын

Awesome!

@sajidsherif5707 4 жыл бұрын

Thanks Mike, that's is really cool.

@excelisfun 4 жыл бұрын

Glad it is cool for you, Sajid : ) : )

@levelzero3D 4 жыл бұрын

Thanks Mike!

@excelisfun 4 жыл бұрын

Glad you like it, Level Zero!!!

@ahmedabdi5160 Жыл бұрын

Excellent

@excelisfun Жыл бұрын

Glad you like it!!!!!

@mobinnaim786 2 жыл бұрын

its great sir thank you so much

@excelisfun 2 жыл бұрын

You are welcome, Mobin!!!

@rash9366 4 жыл бұрын

New school method pretty cool :)

@excelisfun 4 жыл бұрын

Yes, indeed, New School is cool : )

@TaxMentors 4 жыл бұрын

Brilliant

@Dheiva1983 Жыл бұрын

i Really appreciate this video

@excelisfun Жыл бұрын

Glad it helps!!!!

@muhammadnurhakimmohdderis4201 3 жыл бұрын

Love it so much

@excelisfun 3 жыл бұрын

Glad you love it!!!!

@cakeshavardad586 3 жыл бұрын

Awesome

@ashishmohan4707 4 жыл бұрын

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

@excelisfun 4 жыл бұрын

Maybe if item in A2: =CONCAT(FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISERROR(--MID(A2,SEQUENCE(LEN(A2)),1))))

@ashishmohan4707 4 жыл бұрын

@@excelisfun thanx👍👍👍💐

@vijaysahal4556 4 жыл бұрын

very nice 👍👍👍👍

@excelisfun 4 жыл бұрын

Glad it is nice for you,vijay!!!!

@pmsocho 4 жыл бұрын

Thumbs up!

@excelisfun 4 жыл бұрын

Thanks, Teammate : ) : )

@ankursharma6157 4 жыл бұрын

Gratitude!

@excelisfun 4 жыл бұрын

Glad you like it, Ankur!!!

@fernando5166 3 күн бұрын

very good, thanks

@excelisfun 3 күн бұрын

You are welcome!!!!!

@fundips 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Not at first, but once you put cell in edit mode you will get error. It is not backward campatable.

@danielmain3975 4 жыл бұрын

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!

@excelisfun 4 жыл бұрын

Glad it is fantastic for you, Daniel. What is the problem that you are trying to solve with INDIRECT, SUMIFS and XLOOKUP?

@danielmain3975 4 жыл бұрын

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

@excelisfun 4 жыл бұрын

@@danielmain3975 Yes, it could. Do you have different lookup tables on different sheets, or what is the set up?

@danielmain3975 4 жыл бұрын

Exactly! Similarly structured lookup tables throughout a workbook.

@LucasYong31 4 жыл бұрын

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?

@excelisfun 4 жыл бұрын

I am sorry, I am not sure how to do interpolation : (

@teoxengineer Жыл бұрын

If there are two or more dublicate values at same range for looking up, what should we do for calculation?

@excelisfun Жыл бұрын

It depends on what you want to do with dups. Lookup functions only get first one. FILTER gets all matching values.

@planiolro 4 жыл бұрын

Awesome....on quick question: if hypothetically in the column F there are some duplicates how can we fix it?

@excelisfun 4 жыл бұрын

I am sorry, I do not understand what you are asking.

@daeh49 4 жыл бұрын

Columns F and G are SUMIFS columns so potentially every cell could have the same value as are two in the example.

@kumarrohit8538 4 жыл бұрын

Could we find multiple positive number against multiple negative numbers and total comes up to zero...

@gs6810 Жыл бұрын

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?

@henriklarsen2276 4 жыл бұрын

Cant nearly follow u pace.....have to see it again and again.........👍 thx for u great shared skills.👍

@excelisfun 4 жыл бұрын

You can use the gear icon in the lower right below the video to change the speed of the video ; )

@henriklarsen2276 4 жыл бұрын

ExcelIsFun thx, yes i can. 👍 It was fun, i meant u r so good an fast explaning, i nearly cant follow 😀😉

@deepk82 3 жыл бұрын

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?

@excelisfun 3 жыл бұрын

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.

@deepk82 3 жыл бұрын

@@excelisfun thank you!

@pouleichel426 4 жыл бұрын

and what happens when you send a "New school sheet" with xlookup to a person that dosen't have 365?

@excelisfun 4 жыл бұрын

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: kzfaq.info/get/bejne/pNGIm5iAq7HJiJs.html

@nitindube3694 4 жыл бұрын

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

@reng7777 4 жыл бұрын

Xlook up Function si just for excel 365?

@excelisfun 4 жыл бұрын

Yes, XLOOKUP and the Spilled Arrays are only in 365.

@ThatOfficeGuy 4 жыл бұрын

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

@jeffkasavan93 4 жыл бұрын

"That IS amazing!"

@excelisfun 4 жыл бұрын

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

@choisalantv7858 4 жыл бұрын

Is XLOOKUP available in MS 2016 or 2019?

@721MrG 4 жыл бұрын

Unfortunately not. Office 365 exclusive alongside with a bunch of a new functions.

@67duiker 4 жыл бұрын

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

@excelisfun 4 жыл бұрын

Yes! Very cool, 67duiker! But you do need Microsoft 365 Excel ; )

@ankursharma6157 4 жыл бұрын

Brilliant! Thank You for Sharing!

@relaxingmeditation810 4 жыл бұрын

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