Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
Вставка
- Опубліковано 11 жов 2024
- Download Files:
people.highlin...
Duplicate Lookup or Lookup 1 value, return many for a table Part 1. Return winning bets for week from a football betting database. See a formula that will return multiple items when there are two criteria for the data extraction. See an INDEX and MATCH functions formula that uses SUMPRODUCT, COUNTIFS, IF, ROWS, INDEX, MATCH, SMALL, IF, and ROW functions. This is a data extract with 2 criteria using a formula solution. Return Multiple Items From One Lookup Value.
Related Videos: Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row
List Winning Bets With Array Formula
WOW! This is HUGE. I followed this video and have applied it to my workbook and it is now saving me tons of time. Before I had to pull in my data (it was random) and then sort, copy, paste, etc...Now, I just paste it into my work page and Excel does the rest.
I have to say a huge THANK YOU for posting this video. This is awesome.
See Excel Magic Tricks 187, 473 and or 698. All three show various methods of using a formula to extract a unique list.
"Félicitations" from a French ! The best trainer I've ever listened to : Dynamic, explanations, humour ... so natural (exactely the way 'we' search and work our spreadsheets). Moreover, a nice voice. And tricky things become like simple, not to speak about absolutely crazy stuff that offer multiple new openings. Thank you so much for such useful, invaluable fun an knowledge. Merci !
I am glad that the videos help! You are welcome for the videos!
I am glad that you got it working! It is true what you say - the best way to learn is to practice a lot and it is obvious from your comments that you have practiced a lot - do are doing EXCELlent!!!!
I went from knowing just about nothing about Excel except for knowing how to get a sum of a column to trying to replicate this formula. It's slowly coming to me and I reference this video monthly when my sheet breaks for some unknown reason. Thanks for producing, hands down, the most valuable Excel video/tutorial on UA-cam.
It should work in any version. Please download the workbook and check out the files I provide. Watch this video title to learn how to download:
excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks
I am glad that the video helped! (But many others do this sort of thing often, so we are not alone, Lacey!!)
Your enthusiasm and tone of voice is awesome. Makes me want to Excel.
people.highline.edu/mgirvin/excelisfun.htm
Check out playlist of videos all about returning multiple items:
youtube [dot] com/playlist?list=PL63A7644FE57C97F4
You might also check out this playlist with other videos and other methods for extracting multiple records with one or more criteria:
youtube [dot] com/course?list=EC039C9543F229D9D0
There are many ways to do this. One way:
1) If extensions in column A, use a formula like this:
=LEFT(TRIM(A2),6)
2) copy formula down (this extracts the first 6 characters - TRIM is in case there are leading spaces)
3) Sort on column with formula
4) Copy Paste.
You could also Filter, then copy and paste
You could also do Advanced Filter
You could also do a nasty array formula (but only if the data set changed a lot).
THANK YOU, THANK YOU, THANK YOU!!!!!!
Your videos get me over the hurdles that inevitably pop up in many of my projects. I keep meaning to post, as I spend a significant amount of time with your library of videos, throughout the work-day.
I am glad that this video helps!
I really gotta thank you for this video specially. I modified the criteria on this formula to look up all the values starting with 1 for example, within a list. If I use the criteria 11, I'll see more specific results. I did it embedding the LEFT function. It's really useful finding accounting accounts. I could do it also because you taught us how to convert trues to ones. I did that to tell the formula when to stop. I used to read Mr John Walkenbach's books. He doesn't use the counter though.
I have MANY video on the topic of data extraction. Check out this playlist of viceos:
youtube [dot] com/playlist?list=PL63A7644FE57C97F4&feature=plcp
or just go to the excelisfun channel and look through the playlists.
Great! I am glad the video helped!
You are welcome!!
It is amazing what Excel can do!
Search for this "playlist":
Excel Extract Records From Database Table / List
This playlist has many videos on this topic.
I learnt from this vodeo abt return mutiple items from one lookup, finally I managed to solved my return multiples formatting. Thank you very much, Sir!
I am glad that it was useful!
I am glad that it helped, but I am no god, just a guy having fun with Excel.
Mr. Mike i commend you for bottom of my heart to create such a legacy and teach us your experience, you helped me great deal in my carrier development and as a person, because your lessons have some kind of passion which helps me to go for them in my daily routine and develop or refresh my knowledge, My utmost Thanks and gratitude from Georgia!
For the first argument in the INDEX function, you would have to use Mixed Cell References in your named range.
I am not following what you are asking. See this video for information about more than one criteria:
Excel Magic Trick 703: Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filter
This is a very nice video, but as I was working with a large DB set, and I needed to get too many columns from the same data base, so the whole array formula was taking a lot of time to retrieve the data I needed, so instead using the same formula for all of the columns, I created a unique value column as the first column, and retried only that info. for my data base set, and used that retrieved information to do a vlookup to get other columns. It reduced the total time considerably.
Thanks
You are welcome!
I was looking this formula for years, At beginning i was watching so many times to get this done, Thanks so much for this Great Guy, You are Genius.
Keep up the good work at work!
Cool! I am glad fun with Excel leads to increase productivity!
What multiple ways can you use this? (I always like to hear the many ways that people have fun with Excel!)
thx, figured it out, my table was not the full range of 4:260, fixed the tables and working wonderfully!
I asked a question on another video, and this video is the answer to that question. Thank you for this wonderful collection of videos.
Thanks for the quick response. The clip you suggested was very good.
Because the destination data is using an array formula the filter will not sort the data. The only way I can figure it out so far, is to sort the source data and then the destination data follows suit.
@TheDoctor8007 , I am glad thet the video helps!
#REF! means it is looking at a cell reference that no longer exists - so it is probably a problem with the dynamic ranges. For example, if the dynamic range starts in A2 and you tell rnage to look 4 cells up, there is no A-2 cell.
I know nothing about a Mac.
You are welcome!!
I BLOODY LOVE YOU!!!
You have helped me so much doing all these Excel projects at work!
You're a life saver!
I'm in search of such procedure from a very long periods of time, thanks for this video
You are welcome! Thanks for the support, wasif, with your comment, Thumbs Up and Sub : )
EXCELlent!
Thank You!!!! I had such a hard time trying to find out how to do what you just covered... I managed to successsfully adapt the formula to my needs.
Maybe this video:
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?
You are just amazing, don't know how you get such tricky formulas. Beautifully described.
The only success I had was changing the "Sumproduct" function in your file - it worked well with the "Indirect" function inside (this would not work well with the "countifs" function, as the calculated "Countifs" showed the incorrect number).
By the way - great video - as always. Watching your tutorials (and practicing) has taught me a LOT!
I do not know. Try posting your question to this site:
mrexcel[dot]com/forum
When you post at this site, be sure to say how your actual data is set up, what your question is and what results you would like to get. Do not reference this video in your post, just ask your own question - that way people who have not seen the video (99.99% of the people) can answer your question.
Be sure to send me the link so that i can follow along and learn too!
@greatyazer , try these videos:
Excel 2010 Magic Trick 798: Partial Text Lookup Formula To Return Multiple E-mail Records
Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records
Excel Magic Trick 802: Helper Column To Do Partial Text Lookup Formula To Return Multiple Records
Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)
Check for the counting formula in cell C3 - use that one instead of the COUNTIFS. You can send an e-mail to my excelisfun YouTibe account and then I will send e-mail so you can send workbook and then I will take a look.
You can send me an e-mail to:
excelisfun[at]gmail[dot]com
and I can send an example.
Glas the videos help!
Try this playlist:
youtube [dot] com/course?list=EC63A7644FE57C97F4&feature=plcp
Video 703 or 758 or 758.5 may be the best ones to look at for extracting with multiple criteria.
I do not understand your question. For back and forth dialog to get Excel solutions try THE best excel question site:
mrexcel [dot] com/forum
Maybe you can add a second IF condition that says:
anything in range not empty
You Tube does not allow me to type great than or less than symbols, but the condition in the logical test of the IF function would be:
range, greater than symbol, less than symbol, Two double quotes
Thank you this helped me alot. You are an excel god.
try this video:
Excel Magic Trick 584: Dynamic Range for Periodic Data Dumps into Excel OFFSET & Defined Names
If they are from the same column, then this video may help:
Excel 2010 Magic Trick 877: Manager Sales Rep Report: OR Criteria Formula To Extract Records
Excel 2010 Magic Trick 878: Manager Sales Rep Report: OR & AND Criteria Formula To Extract
Excel 2010 Magic Trick 880: Multiple OR Criteria Using MATCH function For Extracting Data Formula
I have a new array formula book coming out in Spring 2013 also.
Data Validation, List. I have a few good videos on this topic:
Excel Magic Trick 548: Data Validation Drop-Down List In A Cell Same Sheet or Different Sheet
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?
Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down
Your formula would have to have 2 criteria:
Range equal Steve
Range greater than 60
Range less than 90
Try just one IF
Thanks!
Than you very much for the kind donation, Roy!!! Although the formulas in this video are how we did it for decades, if you have Micrsoft 365 Excel, then the FILTER function is the new and easier way. Here is the video for that: ua-cam.com/video/XzNfSZBohXc/v-deo.html
I am not sure I understand. Are you saying you are adding extra data below the table? Try the Excel Table feature or Dynamic Range Defined Name Formulas. Here is a playlist of videos about this:
youtube [dot] com/course?list=ECF6A29BD9E1CD5E6A
Thank you very much sir for this great video, really you made my life so easy, now i am using this formula at my work place....
I did it, it works... I'm going to have to practice that multiple times - lmao. Thank you so much for making this. I'm making this video a favorite.
Thanks very very very because of your youtube channel i have learned alot. Thanks again.
Really good and really helpful, but I didn't have time to go through all your videos to figure out how to use the dropdown...
I love your videos.They are so useful and easy to understand. Can I use this Magic trick so that it returns multiple values from a table which I am constantly adding to? I want to be able to create a summary of items much like you have done, based on the name of the person who has received items, but I am constantly adding information into the table.No person will have more than a certain amount of items at any stage,but there are always people being added to the database who receive new items.
Thank you! Very helpful and easy to follow. Which Magic Trick shows how to apply a filter and sort the results? I just can't seem to get that to work as expected.
Very nice video, it is very beneficial for me, thanks
Glad the video helps, Elefe 2!!!
very very nice video!!!
Thank you for this video! I`m getting better and better in excel!!! :-)
Thank you so much
Hi what did you use for the criteria #1 and 2 cell. It doesn't seems to be combo box. May I know what did you use.
Sorry, none on SAP.
sir you have been really very helpful for me.kindly let me know do u have similar videos on SAP.
I do so much filtering with my work and I dont knkw how to except for the FILTET function. 13 Years after this video! OMG
This is an amazing tutorial. It took a couple of times of trial and error before I got it down, but I wouldn't have been able to complete my project without this video!
Is there any way to introduce wildcards into this method? I've had trouble because one of the lookup values references a row with cells that contain multiple values.
Firstly, thank you for your videos... they help me create Excellent Excel solutions for my work.
If I may ask a question, MT 358 in mind, we have 2 criteria here, Week and Win/Loss, How would you go about it if there was a 3rd criteria involved E.g. Month. (Month ‘1’ or ‘Jan’ , Week ‘11’, W or L “W”)
Great videos, I'm now able to do processes I never thought possible.
I do have one question though, have you ever created a video in terms of doing the same formula above but without returning duplicate values?
Amazing trick and well explained !!!
thanks a ton
Thanks Friend its really a great work
Thanks! It's exactly what i was looking for.
Hey bro! We could also use filter on multiple column ... If I am wrong then plz tell me where it can use??
Firstly, thank you for your videos... they are really helped me create Excellent Excel solutions for my work.
If I may ask a question, MT 358 in mind, we have 2 criteria’s here, Week and Win/Loss, How would you go about it if there was a 3rd criteria involved E.g. Month. (Month ‘1’ or ‘Jan’ , Week ‘11’, W or L “W”)
This is exactly what I need!! Thank you so much....just one little thing, I only have ONE criteria to deal with not 2 like you have in this video. I'm trying to modify the formula to handle just one input say the week column. Can you please help me?
Thanks again!
@ExcelIsFun . Awesome. This worked really well! Nice one. The only problem now is that some of my columns are returning in my Summary table have the dreaded #REF in them. I used individual Dynamic Ranges for all the columns in my database table. Any ideas? I am using Mac. Is that a factor?.
You Rock!
hi i've been following most of your videos and they have really been helpful. would you know how to do the same result using vba?
really great video, i was wondering if we can show the rows in reverse order!! how we can do that so the new data stay on the top.
Thank you.
That is slightly different than any videos I have. This site (UA-cam) is not a good place to have back and forth dialog to get Excel solutions. If you post here:
mrexcel [dot] com/forum
Then send me the link to your post, I can try to help.
Try to post a simple data set with your expected results.
Are the two potential criteria from the same column?
Hey!! The link says "Page Not Found".. Nevermind, I found it out anyways.. Keep up the good work.. :)
You are great man, keep up the good work!!!!!
I do have a question more so than a problem... Is there a way to modify the above formula to only show uniques in the column?
Thank you! Another great lesson!
You are welcome!
Mike, am i right?! This method is cool and works wonders! Thanks, I'll buy one of your books for sure! But what if i would like to extract a list from an table with multiple dependent criteria of this same table!? Sorry to bother you, do you have any video that could help? Have a good 2014
Yes, I have many videos about this. Try this playlist of videos:
Excel Extract Data (Records) From Table / List / Database
ua-cam.com/play/PL63A7644FE57C97F4.html
Hi there, fantastic video. Is it possible to recreate using three criteria rather than two?
Hi, great video, it's very useful and easy to understand. But im having a tough time when copying the formula to other cell. It always appear same answer. Sample in your excel A7 formula copied to B7. The answer is the same in A7. Thanks i hope you could help me. Thank you.
This video has helped me tremendously. I'm try to build a staff time sheet log with it, is there a way of searching for one criteria but over more than just one array on another sheet? We have five time sheets we would like to search across and return any matching results to just a single list on one page. Is this possible? Many thanks.
This is really helpful. However I've worked through your example and get to the point of copying the final formula into the cells and I get #NUM! errors for the cells that should appear blank. Please could you let me know why this may be happening?
I have no idea. Send workbook that you are having trouble to:
excelisfun[at]gmail[dot]com
Thanks for the video.
Is it possible to do the same with named tables, instead of regular ranges?
H this is really a useful video,i I followed the video and almost had my spreadsheet done, however I realised that the formula returns one less row than it is supposed to, I can't seem to find the area of problem though. is there any way of fixing this?
This formula looks great, just one problem, my excel 2007 has trouble with data comparisons like "if('358'!$d$16:$d$39="w""part, this result of this comparison lead to #value.