Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items
HTML-код
- Опубликовано: 2 янв 2025
- Download Excel Files:
excelisfun.net...
Download File: excelisfun.net/
See an Array Formula that can extract (from a proper data set) multiple invoice items for one lookup value & show total. See the functions: COUNTIFS, ROW, AGGREGATE, ROWS, INDEX, IF and SUMIFS. See a number of Array Operations in the formula. Learn how AGGREGATE function can handle array operations without using Ctrl + Shift + Enter
FILTER has made this much easier!
agree.
Mike, What a great power dugout in excel. So amassing. Great thinking!. Thank you & May your brain power shine everyday!!!
You are welcome, Jawa!!!
Bill, no matter how complicated it is a formula as you explain it becomes super easy to understand. You are a true magician and an excellent teacher.
Love excel, thanks to you.
Sorry for named you Bill I mean Mike.
My name is Mike excelisfun Girvin, not Bill. But there are lots of really smart Excel guys named Bill, like: Bill "Mr Excel" Jelen and Bill Szysz!!! Glad you like the videos, though.
I have watched others explain how to return multiple results from a lookup and they all start with the Index. For me, your approach of starting with the obvious logical questions (identify the relative row) nailed it. Thanks!
Yes, telling the right story can makes things less difficult to learn. Glad I could help with this, David!
And some people say excel is a stupid program !!! I believe that it is one of the best in terms of specialized and personalized calculations ... you always take it a step further
I will start teaching excel after i learned from your videos, I almost watched all of them many thanks !!!
Wow! That is a lot of videos to watch: 2700! I am glad that the videos help you and soon you will be teaching too!
It is one of the best videos among all made ever on Excel. Thanks, Sir. Keep it up.
You are welcome!
Watching your videos while drunk, shows how fun it is. I've learned so much from you, its amazing.. Thank you!
I like the way you talked through the solution! Many videos just present the formula's from start to finish but you thoroughly explained all the relevant sections and this helped me to understand much better! Thank you so much for your hard work!!
You are welcome, Alex! Glad that you liked the Hows and Whys : )
Mind blown!
It is much easier now if you have Microsoft 365 Excel. Do you have that version? However, the formulas you saw in this video are the ones we have been using for decades : )
Great formula nesting right there!!! Though it takes some time to wrap around it entirely but after understanding it becomes simple but not without the great explanations around it. I appreciate the video
You are welcome for the explanation!
Great work 👏, your are the smartest excel developer at all.....i swear
Not that smart ; ) Just hard working and having fun : )
This is a solution to so many of my problems currently.
Glad it helps your current problems, fakerrain!!! Thanks for your support with your comment, thumbs up and of course your Sub : )
I have trawled loads of tutorials to find this. This is exactly what I was looking for and you explained it very well. Thank you.
You are welcome, Akoola : )
Wow, Mike. What a combination of great functions. Loved the SUMIFS insert and the final total. Excellent.
Glad you liked it!
Wow, advanced formula creation. Very helpful, will require multiple views. Thanks!!
Glad it is helpful!
Brilliant
Glad it helps!
This is brilliant! I never thought that this is even possible without VBA!
Glad it helps, Nikolay! Thanks for your support : )
Thank you very much sir. I needed exactly this and here I got it. We owe you a ton sir for the service you do to us, excel users.
I thought i know everything i need in excel, but always we have better method, thank you for this vedio
You are welcome!
You are great Sir, my job is just because only you and your videos. God bless You Sir with my deep heart. Thank You So Much
You are welcome so much, Asif!!! I am glad that the videos help in your job. Thanks for helping me with those comments and thumbs ups on each video and your Sub : )
Hey 'F FOURR' you're a genius! :)
Totally awesome and excellent as usual. ExcelIsFun & Mike Girvin is for true excel lovers.
I am glad that this helps : ) Thank you as always for supporting excelisfun with your comments and Thumbs Up : )
One of my many favourites
This is a cool formula trick to have up your sleeve : )
very clever formula for adding and expanding ranges .. thanks for sharing your knowledge
You are welcome!
This is exactly what ive been looking
for for the last 10months i guess cos i cant mess with that vba thing. Thank you!
Awesome, great, smart & distinguished as usual.
Glad it helps you, Ebrahim!!!!!
The total at the end is cool!
Cool and fun! : )
You're an excel wizard. Thanks for the great video.
You are welcome, Ryan!
Amazing formula! I usually use helper column(s) to get the list, but this is one nice array formula, hands down. The only thing that puzzled me is about the data. What I usually see is datasets with unique Item # for Item names. Duplicated names for one single Item # does not seem to make sense to me. But definitely great solution. A big fan of ExcellIsFun.
"Duplicated names for one single Item # " can occur in proper data sets when there are multiple items sold for a given invoice number, where invoice number is item # in this case.
Got it, Mike. Many thanks!
amazing video ,learnt a lot from your video thanks to dedicate your time to share the coolest technique
You are welcome!
Great excel info thanks
Do you have sample for multiple criterias?
Can this formula be made to reference a value of greater than zero instead of a customer number? This formula is so close to pulling all the line items I need from a list that has totals.
OT quick question Mike. Using Excel 2010, I can double click any cell containing data in pivot tables and get all the entries for that particular cell onto a new sheet. For example, if I want to know where the $100 total in a cell comes from, I just double click on the cell and a new worksheet pops up listing all the entries that added up to $100 for that cell's category.So far so good.However, when I create a slicer for that pivot table, and use it to filter it, let's say according to salesperson, if I were to do the same thing to see that salesperson's results for the same cell (let's say, it now says $50), the sheet that pops up does NOT give me a listing of just the entries that added up to $50. It gives me a lot more entries and I have to hunt to find the ones that added up to $50 for that salesperson.HELP!
I am not sure what is going on.
Hi Mike, is there a way I can send you some screenshots?When I build a simple pivot table from my data set, I can double click into a cell containing data to retrieve all the entries that make up the total for that data cell.For example, if I'm tracking product sales, I'm collecting data for product, brand, sales person, sales area, as well as the sales. So, five pieces of data for each entry.I built a pivot table using product as the row label, brand as the column label, and sales amount sum as the value. My pivot table then tells me my company's sold $284.18 worth of bubble gum, and when I double click on that cell, a spreadsheet opens up with four records telling me that this amount was shared between three salespersons and the sales from each person.Now, when I use the sales area slicer and select one area out of five total, the pivot table values change (naturally). What was originally $284.14 now reads $113.98 because the latter amount was sold within that specific sales area. So far so good.However, when I double click the cell that now reads $113.98, the spreadsheet that opens up still has the original four records totalling $284.14. It still assumes the slicer didn't narrow the search conditions despite the pivot table already changed to the new values because of the slicer selection.How can I make the pivot table just pull up the records based on the slicer selection when the cell is double clicked?Thanks Mike!Ed:-)
I am stressed for time - try posting question to Mr Excel Message Board: mrexcel.com/forum
Ok. Thanks anyways.
Hey Mike, I'm sure you know the answer, but a guy on the Mr. Excel Message Board was able to point me in the correct direction. Apparently, I needed to select the same category in the Pivot Table's "Report Filter" that I have in my slicers. This then allows drilling down into the Pivot Table cells after the slicer selection is made. I then just hide the rows of the Report Filter categories.Also, it appears that the problem no longer exists in the 2016 version (but I haven't verified if this is true or not).Lastly, I learned through trial and error of just useful the GETPIVOTDATA is when creating summary tables. I was going crazy that my INDEX-MATCH was giving me incorrect responses when the slicer shrunk the Pivot Tables when a column or row category was missing.You guys are freaking geniuses!
Hi thanks sir great work
thank u very much sir for your value able suggestion sir
You are welcome very much, Prem!!!
what if my sales column has negative values and the sumif function is giving me the negative values how can I bring this values as normal.... plz help
Wow! Just wow. How in the heck! You sir are a master.
Just having fun! Glad you like the video!
This will help , thank you
You are welcome!
I have been enjoying your videos for a few years now, and each time I learn so much more than I was expecting to. I boast about ExcelIsFun when my friends and I talk about different projects we are in the middle of at our companies and how helpful these videos have been.
Though, right now I've run into a issue that has gotten me baffled, and I have watched several videos that give me a really close answer, but is off by just a small bit, and I am unable to finish it off. Is there a way that I can submit a sheet to have you look at and explain how, or if, it would be possible pull the data that I am looking for?
For back and forth dialog to get Excel solutions try: mrexcel.com/forum
Thank you, I will try that. I was able to figure out the issue that I was having with my project, and got it to work. Again, your videos are awesome and I learn so much from them each time I watch!
Thanks Mike for the trick. I learnt a lot from your magic tricks.
You are welcome! Thanks for the Thumbs Up!
In your work book in sheet no 23-(18) if I delete region criteria than I want other records between two dates are extract
Wow, seriously great
Glad it helps, Bas!!!
seriously mindblown. thanks so much!
You are welcome so much!
When I have extract records between two dates and other criteria when I delete other criteria so extract records between two dates not extract
Thanks Mike! I was wondering if there you can use AGGREGATE formula to return a list of items based on two or more criteria (e.g. invoice and date greater than a certain date)?
Awesome Mike with EXCELlent video
Hi! This formula set up is already what I need. However, I am struggling with the following: if I want to use this formula set up to lookup values and in case I have 2 times the same value the formula ranks both as number 82 (in my particular case) which is not what I need. Any idea how we can rework the formula so it allocating for each value a individual rank even if we have duplicate values?
Amazing formula! Thank you very much.
You are welcome very much, Robert! Thanks for the support with your comment, Thumbs Up and Sub : )
aggreate function 😀. new thing should be learn. thanks for valuable course
You are welcome for the valuable course!
+ExcelIsFun I have started to learn from your videos for over 2 years. Love its as the first days studying. Excel is magic and you are a magician 😀
That is awesome! Do you have coffee or tea first thing in the morning with the Excel Magic?
+ExcelIsFun : yes. of course
Great 👍
Great and well solved..
Glad it is great for you, henry!!! Thanks fro the support with your comment, Thumbs Up and Sub : )
Hi, how about multiple items from two lookup value ?
This is a great video, thank you! the more you added to it, the more I smiled out of the complexity and brilliance of it. This solves a problem I have (I think), however, how would you do most of this (minus the sum below the item), and have the sum (or in my need the quantity) in the G8:G17 cells next to the corresponding item.
How do I remove the totals
This is just what I'm looking for, only AGGREGATE is not on my older version of Excel.
I know that this video is OLD but it helped a lot.
I do need help though. I would like to use this same formula, Then be able to rank those same Items based on a certain criteria.
Can you do it?
shall i always fixed the related cell? i mean the multiple cell and get the information? as i am doing on row basis and all data does not work with ROWS function !! can you advise please?
i have a list made up of text only. how do i look up a text data that shares two specific text values?
Incredible, just what I needed! Thanks a lot!
Glad it was incredible for you, Oscar !!! Thanks for your support with your comment, Thumbs Up and of course for your Sub : )
Excellent video just wondering what part of the formula I have to remove if I am just interested in the total sales by item? I will appreciate if you can help with this.
using offset （one cell ) to replace index (array) can simply the index array operation
Can u please explain the same thing for Google sheet..
Hi Mike that is superb! but was wondering what if the the rows of data increases? was there a way of making it dynamic maybe?
Keep source data in an Excel Table (Ctrl + T).
Thanks Sir...
Hi Mike , I need (1311an) only row F on my front page ( eg Sheet1 ) i have different data sheets underneath on ( Sheet2 - Sheet24 ) How do i combine all sheets to look up data on Sheet 1 - pls help
Hats off...You are amazing
i have a question.
there are two tables (created with ctrl + t command) on the same sheet
in first table there are two headings
1. subject
2. marking type (means particular subject will be awarded grades or marks)
(for example there are four subjects i.e. ENGLISH , MATH, DRAWING, SPORTS, MUSIC)
[FIRST TWO SUBJECTS WILL BE AWARDED MARKS AND REST OF THREE WILL BE GRADED]
now there is second table having one column
1. grading subject
aim of creating second table is to show only grading subjects from ist table WHICH ARE DRAWING SPORTS AND MUSIC
XLOOKUP NOT WORKING
IS THERE ANY OTHER OPTION?
Thanks a lot Mike. But I have a slight problem with the k in the small formula. Generally, in which circumstances is one supposed to use COUNTIF or ROWS function? I have seen you using either of the two, and am not yet aware of circumstances for using them. Thanks
ROWS is when you just need to extract a sequential list, like: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10... and so on. COUNTIFS is when there is a criteria in the rows of the extract area and you need something like: 1, 2, 3; 1, 2; 1, 2, 3, 4; 1; 1, 2...
Example of ROWS:
Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items
ruclips.net/video/mkQhBBE1PqQ/видео.html
Example of COUNTIFS:
Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates
ruclips.net/video/rKDI-kdBsjY/видео.html
Thanks for clicking Thumbs Up on all the excelsifun videos you watch! : )
Thumbs up.
Thanks sir! That is wonderful. Is it possible by filter formula. Thanks sir.
Yes, it is MUCH easier with the New Office 365 FILTER Function (Dynamic Arrays). I made a video about it here: ruclips.net/video/5zRab2Grz7Q/видео.html
Let me know what you think, Irfan!!!!
@@excelisfun Thanks sir. You are a great teacher. I pray you excel at anything you set foot at like you excel at excel!
@@irfanbashir1314 Thanks for your support : )
Wow amazing thanks!!
You are welcome!
Hey Mike. Long time no write. I've finished my thesis and it was about Excel, had you as reference a lot of times. :)
I've also bought both Ctrl+Shift+Enter and Slaying Excel Dragons (so cool that I have a kindle and can read during travel easily). Do you have any other books out yet? You planning on making more?
Have a nice day & Take care,
Congratulations on writing your thesis! I wish that I had more books, but I don't like wriing books much, I tend to do better with making videos. I do have at least one more book to write, but no time right now. There will be a new priniting of Ctrl + Shift + Enter with new cover and Table of Contents coming out in a few weeks, if you want one for your collection!
So a big question now: you have the ability to extract this specific type of data, how would you extract this type of data BETWEEN certain dates? Example w/ Ledger: extract data found about expenses like "paper" for description of the company bought from and also cost, but how would you extract that same data with time limitations of specific year or even months?
Better import your data to ms access and run a query that suffice your given criteria. Excel is better in analysis and presentation. Access is better in storing large data and extracting the data you need.
I'm so confused by the formulas! How did you do the drop down item selection thing at the top?
Select the cell , then Go to Data > Data Validation > select from list
+Ahmed Saad Thank you!!
Wow!..just Wow!
I dont know why but this didnt work for me, tried freezing the cells, and every paranthesis comma that i could, but the whole formula is not working because when i do f9 on =ROW($B$2:$B$367)=$I$1, the division by zero error shows for each reference, the lookup value is also identical to the one in the list.
I saw a previous version of this video 358 which was an array formula, that seemed to worked in my home system which has excel 2010, and it is somehow throwing up an error at my workplace which is using 2016
Something I'll never figure out by myself, génial!
gLAD YOU LIKE IT!
Thanks a lot!) Any lessons on excel VBA coming? think they can be useful as well
I am not good with VBA so I don't do lessons... Here are two good VBA channels:
ruclips.net/channel/UC9OIUFZfYqELCFwWxT7OpKQ
and
ruclips.net/channel/UC-vzNYU9x8IYPk_r89mGvXA
that's brilliant
Glad you like it, Atul!!!
Question what if there are two items # how would it display the items sir? Thank you!
Mind blow ... Thanks
You are welcome!
Thanks a lot, Mike! This is really helpful! Say I had to do the reverse - if I had to find all the sales $ for a particular item number and only return the corresponding item name for the max sales $, how would I go about it? For e.g., in the current example, item number "15471-BN" should return "Quad" because the sales $ for it is $584 which is the max of sales $ of all the four items (Sunshine, Bellen, TriFly & Quad) under "15471-BN". Any help is appreciated :)
To find max sale for each product you can use a pivottable or maxifs function.
I will try to make a video
Hey Mike, what's new in your Control-Shift-Enter blue cover book? I have the book with the green/yellow cover :)
I love your videos. I think you are definitely one of the best! is there a way to include date criteria in this formula? For example, lookup up anything before 9/1/2016. Looking forward to your response. Thank you!
Mike,nice trick
How did you do that. Super amazing
Superb! He is genius!
Super
thanks for this trick, I was always wondering if it is possible and how. I can feel this will be really useful for me.
You are welcome! I am glad it is useful for you!
Hi Mike, love Your videos and how you solve lots of problems with the use of excel!
I've trawled some of them to trying to find one that had the answer to my problem, but havn't had any luck yet - thats the reason im writing to You now.
My problem is that I have 2 tables one that contains a list of partnumbers, belonging machinery and production time, and the other a list that contains partnumbers, Purchaseorders, Deliverytime etc.
In table 2 there can be more than one Purchaseorder for each partnumber sometimes with the same deliverydate othertimes a different date.
I would like to create that shows all purchaseorders from table to for each partnumber in table 1.
Does it make any sence to You and can it be done or maybe already have been done?
Br
Anders
Hello, I want to apply this amazing function in Google spreadsheet but the aggregate function can't work on Google spreadsheet, what to do??
I suggest you use a professional Invoice Maker app, such as RapidBooks. Sometimes using Excel will encounter various problems.
Solving these problems will take a lot of time. It’s better to use RapidBooks, which will be easier and look more professional
fantastic!
Glad you like it!
hi mike. I dont have the function aggregate and i can do that.
how can have this? download it?
AGGREGATE is in Excel 2010 or later.
Brilliant :D
Fantastic
Glad it is fantastic for you, ali!!! Thank you for your support with your comment, Thumbs Up and Sub : )
Thank you a lot Mike, that's very cool and i thank you for helping me to find a solution to my requestt tahnnnnnnnnnnnnnnnkks a millionnnn
You are welcome!
Aggregate function = rad, ExcelIsFun = rad!
Thanks, my Rad Friend Kevin!!!
You pulled a rabbit out of the hat .... yep!, you are a true magician :-))
I must respectfully disagree, Most Amazing Bill Szysz!!! : ) You are a real Excel Magician!!!! I am just a good Excel story teller!!! : )
....and I'm just a avid long time ExcelisFun listener :P
Great video MG!
Glad you like it, long time watcher!!!
Sir, can you please teach us how to run a macro in a protected sheet on your next tutorial? I can't do it after trying so many times. I need it immediately. Please help us sir.
As I say in many of my videos, I am not good with VBA and so I don't make videos on that topic.
Sir, Do you know someone who can do it (run a macro in protected sheet) without any vba code. Please tell him. I need it very much. I want to make a project which is password protected. So I need it very much sir.
Genius!
Glad you like it!
Just wow
Glad it helps!!!
Genius
Glad the technique helps, Jayesh!