Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items

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

[@MaydayAggro]

FILTER has made this much easier!

[@Retumn98716]

agree.

[@Ingolf00001]

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.

[@Ingolf00001]

Sorry for named you Bill I mean Mike.

[@excelisfun]

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.

[@davidcchambers]

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!

[@excelisfun]

Yes, telling the right story can makes things less difficult to learn. Glad I could help with this, David!

[@jawaherath8206]

Mike, What a great power dugout in excel. So amassing. Great thinking!. Thank you & May your brain power shine everyday!!!

[@excelisfun]

You are welcome, Jawa!!!

[@arttube13]

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

[@excelisfun]

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!

[@nicci113]

Watching your videos while drunk, shows how fun it is. I've learned so much from you, its amazing.. Thank you!

[@ابراهيمالرجب-ه9ط]

Great work 👏, your are the smartest excel developer at all.....i swear

[@excelisfun]

Not that smart ; ) Just hard working and having fun : )

[@alexmeredith6045]

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

[@excelisfun]

You are welcome, Alex! Glad that you liked the Hows and Whys : )

[@akoola7853]

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.

[@excelisfun]

You are welcome, Akoola : )

[@sim7717]

It is one of the best videos among all made ever on Excel. Thanks, Sir. Keep it up.

[@excelisfun]

You are welcome!

[@joelngige5776]

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

[@excelisfun]

You are welcome for the explanation!

[@kjvstats9003]

Wow, Mike. What a combination of great functions. Loved the SUMIFS insert and the final total. Excellent.

[@excelisfun]

Glad you liked it!

[@cato451]

Wow, advanced formula creation. Very helpful, will require multiple views. Thanks!!

[@excelisfun]

Glad it is helpful!

[@fakerrain]

This is a solution to so many of my problems currently.

[@excelisfun]

Glad it helps your current problems, fakerrain!!! Thanks for your support with your comment, thumbs up and of course your Sub : )

[@muthukumarneelakantan3276]

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.

[@Al-Ahdal]

Totally awesome and excellent as usual. ExcelIsFun & Mike Girvin is for true excel lovers.

[@excelisfun]

I am glad that this helps : ) Thank you as always for supporting excelisfun with your comments and Thumbs Up : )

[@nikolayiliev8865]

This is brilliant! I never thought that this is even possible without VBA!

[@excelisfun]

Glad it helps, Nikolay! Thanks for your support : )

[@pmsocho]

The total at the end is cool!

[@excelisfun]

Cool and fun! : )

[@excellearn8473]

This is exactly what ive been looking for for the last 10months i guess cos i cant mess with that vba thing. Thank you!

[@Asi.Qureshi]

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

[@excelisfun]

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

[@ryannoe86]

You're an excel wizard. Thanks for the great video.

[@excelisfun]

You are welcome, Ryan!

[@19761999]

Mind blown!

[@excelisfun]

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

[@Scream.9]

I thought i know everything i need in excel, but always we have better method, thank you for this vedio

[@excelisfun]

You are welcome!

[@Camille_Fox]

Hey 'F FOURR' you're a genius! :)

[@ebrahimhamza6882]

Awesome, great, smart & distinguished as usual.

[@excelisfun]

Glad it helps you, Ebrahim!!!!!

[@johnborg6005]

One of my many favourites

[@excelisfun]

This is a cool formula trick to have up your sleeve : )

[@jazzista1967]

very clever formula for adding and expanding ranges .. thanks for sharing your knowledge

[@excelisfun]

You are welcome!

[@lennywu4859]

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.

[@excelisfun]

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

[@lennywu4859]

Got it, Mike. Many thanks!

[@babu_frik]

Wow! Just wow. How in the heck! You sir are a master.

[@excelisfun]

Just having fun! Glad you like the video!

[@jeanyveslaval4293]

amazing video ,learnt a lot from your video thanks to dedicate your time to share the coolest technique

[@excelisfun]

You are welcome!

[@VijayendranRaghavendran]

Thanks Mike for the trick. I learnt a lot from your magic tricks.

[@excelisfun]

You are welcome! Thanks for the Thumbs Up!

[@iankr]

Brilliant

[@excelisfun]

Glad it helps!

[@Tang63]

seriously mindblown. thanks so much!

[@excelisfun]

You are welcome so much!

[@aykumar26]

Great excel info thanks

[@oscarrodriguez9522]

Incredible, just what I needed! Thanks a lot!

[@excelisfun]

Glad it was incredible for you, Oscar !!! Thanks for your support with your comment, Thumbs Up and of course for your Sub : )

[@robertlevak]

Amazing formula! Thank you very much.

[@excelisfun]

You are welcome very much, Robert! Thanks for the support with your comment, Thumbs Up and Sub : )

[@pucca9090]

aggreate function 😀. new thing should be learn. thanks for valuable course

[@excelisfun]

You are welcome for the valuable course!

[@pucca9090]

+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 😀

[@excelisfun]

That is awesome! Do you have coffee or tea first thing in the morning with the Excel Magic?

[@pucca9090]

+ExcelIsFun : yes. of course

[@MUHAMMADAHMAD-bt5ny]

Hi thanks sir great work

[@forzaalgiers2045]

This will help , thank you

[@excelisfun]

You are welcome!

[@PremSingh-ry7ek]

thank u very much sir for your value able suggestion sir

[@excelisfun]

You are welcome very much, Prem!!!

[@SyedMuzammilMahasanShahi]

Awesome Mike with EXCELlent video

[@basengelblik5199]

Wow, seriously great

[@excelisfun]

Glad it helps, Bas!!!

[@henkas3]

Great and well solved..

[@excelisfun]

Glad it is great for you, henry!!! Thanks fro the support with your comment, Thumbs Up and Sub : )

[@edwardwatson8937]

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!

[@excelisfun]

I am not sure what is going on.

[@edwardwatson8937]

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

[@excelisfun]

I am stressed for time - try posting question to Mr Excel Message Board: mrexcel.com/forum

[@edwardwatson8937]

Ok. Thanks anyways.

[@edwardwatson8937]

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!

[@ابراهيمالرجب-ه9ط]

Great 👍

[@photonatjag]

This is just what I'm looking for, only AGGREGATE is not on my older version of Excel.

[@h.1899]

Hats off...You are amazing

[@ohdjrp4]

Wow!..just Wow!

[@hollywoodbrad73]

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.

[@dandelincheng9902]

Something I'll never figure out by myself, génial!

[@excelisfun]

gLAD YOU LIKE IT!

[@rodelmar1]

Wow amazing thanks!!

[@excelisfun]

You are welcome!

[@AtulKumar-hp1ip]

that's brilliant

[@excelisfun]

Glad you like it, Atul!!!

[@RealSacredCow]

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?

[@excelisfun]

For back and forth dialog to get Excel solutions try: mrexcel.com/forum

[@RealSacredCow]

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!

[@fritzschuck6474]

Do you have sample for multiple criterias?

[@poom_laupat]

Mind blow ... Thanks

[@excelisfun]

You are welcome!

[@madhavam718]

Super

[@hailv3739]

fantastic!

[@excelisfun]

Glad you like it!

[@ashishmishra9922]

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

[@MUHAMMADAHMAD-bt5ny]

In your work book in sheet no 23-(18) if I delete region criteria than I want other records between two dates are extract

[@dericcheung8904]

using offset （one cell ) to replace index (array) can simply the index array operation

[@joshskiles7135]

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?

[@amitkumar-ip6yv]

Mike,nice trick

[@tedchang2711]

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

[@aliteymouri7035]

Fantastic

[@excelisfun]

Glad it is fantastic for you, ali!!! Thank you for your support with your comment, Thumbs Up and Sub : )

[@drewf141]

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.

[@MUHAMMADAHMAD-bt5ny]

When I have extract records between two dates and other criteria when I delete other criteria so extract records between two dates not extract

[@Baluma1]

thanks for this trick, I was always wondering if it is possible and how. I can feel this will be really useful for me.

[@excelisfun]

You are welcome! I am glad it is useful for you!

[@wescartwright5475]

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?

[@hexsplays]

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.

[@hjiraoussama776]

Just wow

[@excelisfun]

Glad it helps!!!

[@efrenflores7900]

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.

[@mohamedchakroun4973]

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

[@excelisfun]

You are welcome!

[@LvCatsify]

Hi, how about multiple items from two lookup value ?

[@mahsekerpolat729]

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?

[@saadmajoka7577]

Genius!

[@excelisfun]

Glad you like it!

[@irfanbashir1314]

Thanks sir! That is wonderful. Is it possible by filter formula. Thanks sir.

[@excelisfun]

Yes, it is MUCH easier with the New Office 365 FILTER Function (Dynamic Arrays). I made a video about it here: kzbin.info/www/bejne/a6u1kpVofNetbbM

[@excelisfun]

Let me know what you think, Irfan!!!!

[@irfanbashir1314]

@@excelisfun Thanks sir. You are a great teacher. I pray you excel at anything you set foot at like you excel at excel!

[@excelisfun]

@@irfanbashir1314 Thanks for your support : )

[@MrSarky1992]

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,

[@excelisfun]

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!

[@Knight-Ai200]

How did you do that. Super amazing

[@irfanbashir1314]

Superb! He is genius!

[@shau78]

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

[@excelisfun]

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 kzbin.info/www/bejne/o5y0mXV4epaDp7M Example of COUNTIFS: Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates kzbin.info/www/bejne/qHynemChmaemoLs

[@excelisfun]

Thanks for clicking Thumbs Up on all the excelsifun videos you watch! : )

[@excelisfun]

Thumbs up.

[@muhammadhendra6844]

Brilliant :D

[@moemotala6758]

How do I remove the totals

[@ublogs]

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?

[@BillSzysz1]

You pulled a rabbit out of the hat .... yep!, you are a true magician :-))

[@excelisfun]

I must respectfully disagree, Most Amazing Bill Szysz!!! : ) You are a real Excel Magician!!!! I am just a good Excel story teller!!! : )

[@Sal_A]

....and I'm just a avid long time ExcelisFun listener :P Great video MG!

[@excelisfun]

Glad you like it, long time watcher!!!

[@jayeshsuthar8161]

Genius

[@excelisfun]

Glad the technique helps, Jayesh!

[@mrexcel173]

Can u please explain the same thing for Google sheet..

[@samuelnderitu2273]

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?

[@excelisfun]

Keep source data in an Excel Table (Ctrl + T).

[@samuelnderitu2273]

Thanks Sir...

[@qurankareem53]

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?

[@MySpreadsheetLab]

Aggregate function = rad, ExcelIsFun = rad!

[@excelisfun]

Thanks, my Rad Friend Kevin!!!

[@greza5710]

i have a list made up of text only. how do i look up a text data that shares two specific text values?

[@MadhuraTaskar]

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

[@excelisfun]

To find max sale for each product you can use a pivottable or maxifs function.

[@excelisfun]

I will try to make a video

[@rabit4815]

Thanks a lot!) Any lessons on excel VBA coming? think they can be useful as well

[@excelisfun]

I am not good with VBA so I don't do lessons... Here are two good VBA channels: kzbin.info/door/9OIUFZfYqELCFwWxT7OpKQ and kzbin.info/door/-vzNYU9x8IYPk_r89mGvXA

[@ryanelf1738]

I'm so confused by the formulas! How did you do the drop down item selection thing at the top?

[@ia7meDi]

Select the cell , then Go to Data > Data Validation > select from list

[@ryanelf1738]

+Ahmed Saad Thank you!!

[@mohamedhijazi8460]

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!

[@BilalAhmed-to4xp]

Hello, I want to apply this amazing function in Google spreadsheet but the aggregate function can't work on Google spreadsheet, what to do??

[@Chinchin-cc4he]

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

[@chrisbotha549]

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

[@kevinjurado5741]

Question what if there are two items # how would it display the items sir? Thank you!

[@javadkhalilarjmandi3906]

these formulas are awesome, and like you always say they are nasty!!!but if you apply that for a big dataset, it gonna take a long time to refresh. by big data i mean more than 3000 rows

[@excelisfun]

Yes, the #1 downfall or negative for Array Formulas is that they take a long time to calculate of larger data sets. In the book I wrote about Array Formulas, I discuss this topic extensively. Thanks for the support, javad : )

[@prashantiyer7]

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

[@kartickchakraborty7948]

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.

[@excelisfun]

As I say in many of my videos, I am not good with VBA and so I don't make videos on that topic.

[@kartickchakraborty7948]

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.

[@andersbrinchmann3356]

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

[@danboutsi3228]

hi mike. I dont have the function aggregate and i can do that. how can have this? download it?

[@excelisfun]

AGGREGATE is in Excel 2010 or later.