Excel Magic Trick 986 One Lookup Value, Extract Multiple Items, Display Horizontally (A Closer Look)

[@excelisfun]

You are welcome!

[@ExcelStrategy]

I know that to prepare such kind of complex video require a very great effort , only a person with a real passion can do it.

[@MarkLee-cx5es]

Thanks Mike , i'll Try Them Both

[@wanghongsu6484]

I searched 17434 names against a 4000 name reservoir with 20 possible output using your formula, and it worked. Thank you, such a cool channel!

[@excelisfun]

Glad the video helped, Wang!!!

[@excelisfun]

I use the program Camtasia. As long as you act within the law, you don't have to ask Microsoft.

[@MarkLee-cx5es]

Both Worked Fine,Thanks Again ,U R Doing Good Job Here!!

[@krn14242]

Thanks Mike

[@ExcelStrategy]

I wonder how many people out there whatching this video fully understands what's going on inside these huge array formulas! Back to the video, I prefer to construct the comparative vector with the row-indirect-len but i think its a matter of preference :). I didn't know that the "IFERROR" is slow in some situation and using "If + a counter" is faster. Last thing microsoft shuld improve the "Aggregate" and enable it to handle array even with the other functions non only from 14 to 19.

[@simfinso858]

Masterpiece.

[@excelisfun]

Thanks, Phone Excel!!!

[@WadeRaef]

Mike...your guru skills have elevated me to the next level here at work!

[@excelisfun]

+Versus AI I am glad that the Excel stuff I put out helps! It is fun to be the Excel guru at work because everyone must use Excel well and hardy any one knows how and thus: Excel Super Hero to the rescue!

[@mucdoc]

how are you guys today

[@ExcelStrategy]

Yup im too mouch enthusiast and wrote my comment too fast :) , in a case like this one in place of LEN i use ROWS. Anyway maybe your solution calculates mouch faster. Another thig that I did not mentioned in my previus comments is the way you obtain the the relative position in the column beside the names ! I Find in it a great potential !

[@excelisfun]

Cool!

[@sayefuts]

Mike,I just don't have words to say thanks to you!! You are awesome!!

[@saqlainsuleman2179]

Thank you! Spent ages trying to work this out and your video really helped!

[@excelisfun]

Glad the video helps, saqlain!!! Thanks for your support on each video with your comments, thumbs ups and your Sub : )

[@excelisfun]

Yes, this is a common construction to get an array of numbers based on the number of characters in a cell: ROW(INDIRECT("1:"&LEN(cell))), But what confused me is that it is not applicable in this video's situation...

[@excelisfun]

The best way to learn Excel at excelisfun at KZbin is to watch Excel Basics playlist and then Highline Excel Class playlist.

[@excelisfun]

The main advantage that formulas have over PivotTables, Filter, Sorting is that they instantly update when source data updates. This is not always a requirement of the project at hand and so PivotTables etc., can be quite an advantage. However, in this example, I don't know how to make PivotTable do this. PivotTables make aggregate calculations with 1 or more criteria. If we did a Pivot for Phil for Number column, it would add or other aggregate calculation. This example is just data extraction.

[@MarkLee-cx5es]

Hi Mike,Thanks For The Great And Hard Work,Could u Tell Me How To Use LOOKUP To Find Last Number In A Column Excluding Zeros?? Thanks In Advance

[@excelisfun]

Cool! I am glad that these videos help! It does help that it is a lot of fun to do (that is the passion part you are referring to)!!

[@MarkLee-cx5es]

Thanks Mike ,All Four Formulas Did The Job Perfectly !!! I Appreciate Your Help, Thanks A Million !

[@excelisfun]

Very nice!

[@excelisfun]

I am unclear about what you are asking. Can you re=state your question?

[@ExcelStrategy]

It's a very old construct, i don't even remember when Iv seen it the first time, probably in the misterexcel forum some years ago. ROW(INDIRECT("1:"&LEN(cell))) I use Len if Im working on a text string inside a cell and ROWS(range) if im working with a range of cell like in this case in the video. Maybe your solution it's faster becouse it invokes only one function while the one I use invokes 3 functions.

[@excelisfun]

Processor speed will help.

[@excelisfun]

I do see the ending. Maybe try to load it again?

[@tt350zJason]

Once again you have come through my friend and provided me with the answers when I was completely lost! Many thanks as my co-workers are wondering how I make these spreadsheets work but that is my little secret. Virtue follows those who are resourceful! :)

[@excelisfun]

jason hachtel , I am glad that the videos help!!!

[@clorox7907]

How do you display the output vertically?

[@excelisfun]

I don't know if I am familiar with "row-indirect-len". What is that? Yes, there are some situations that IFERROR is great and some that it is quite slow for formula calculation time.

[@hafidztry]

hai MIke, is there a way when we use lookup to return to #N/A when the table rows show zero. is it possible.

[@anmolsingh8656]

The formula u have given invokes in one cell only when i pull the raabit to the horizontal there's #NUM! Showing in next 2, 3 cell horizontally

[@anmolsingh8656]

Help me out

[@anmolsingh8656]

@6:00 min over to the side ...#num #num is showing in relative position in my excel ... It never change till to end how can put formulaes over to the side

[@excelisfun]

Do you mean use MATCH(Range,Range,0) in place of ROW(range)-ROW(FirstCellInRange)+1? I am not following how this will work. MATCH(Range,Range,0) does not generate {1;2;3…}. What am I missing?

[@excelisfun]

Yes. Year over year calculations can be done vertically or horizontally. Here is a playlist of videos on year over year calculations: Excel Reports to Compare Months, Quarters, Years youtube [dot] com/playlist?list=PL3C9D76E9A2F51440

[@voodooman08]

is it youtube that cut the end?)

[@ironmason]

Sorry, I’m cannot follow. What is the use of ctrl + shift + en?

[@ethelgraceabaniel1160]

hi, how did you do your drop down list cause mine duplicated data

[@excelisfun]

Here are two more: =LOOKUP(2,1/(range>0),range) =LOOKUP(1,--(range>0),range)

[@axeldaval3410]

When you realize half way that this is wayyyyy above your capabilities. Kudos to you :D

[@suzettesims6400]

What if my lookup is not a single lookup but an array and I need to display it the values vertically. Can I use the ROW function in place of the COLUMN function? I am building a report with part numbers and Work Order quantities and I have to pull in the purchase order quantities from another report. Both reports have some part numbers multiple times. The PO quantities will be used with the unit price to get the monthly dollars.

[@Amatureithelpstation]

What kind of screen capture software do you use? Do you monetize these videos? If so do you have permission from microsoft? Is that permission necessary? I have learned a ton from your videos. Thank you for your time and great job.

[@pmsocho]

Mike, you are explaining that stuff so EXCELlent! I guess it is one of the examples from the upcoming book :)

[@rachelb2600]

Thanks for providing the link. I appreciate your help!

[@Iheartfrogger]

Thanks so much! Clear and thorough explanations, unlike 90% of internet excel tutorials!

[@excelisfun]

Actually, this is a good rehearsal for that chapter... It's all about telling a good story... Thanks for the EXCELlent compliment!!

[@excelisfun]

Maybe: =SUMIF(C14:C22,"Phil",B14:B22) or =SUMIFS(B14:B22,C14:C22,"Phil")

[@Trogdorfan107]

This was well above my level as a newbie, but I managed to get it to work through judicious copy and pasting your arguments. Thanks so much, these videos really help my work!

[@walterclam]

Mike, your video answers all the questions in my mind on this topic, thanks a lot!

[@excelisfun]

Glad the video helps!

[@jeyakumarvj]

THANK U SO MUCH BRO👍😇

[@excelisfun]

You are welcome, Bro! Thanks for your support with comment, Thumbs Up and Sub : )

[@jeyakumarvj]

sure bro👍

[@rachelb2600]

Thanks for your help!

[@alkey00]

As always great educational video. I would like to offer a little bit simpler solution to this problem: =IFERROR(INDIRECT("B"&SMALL(IF($C$14:$C$22=$F$14,ROW($C$14:$C$22),""),(COLUMN(A1)))),"") This formula is smaller and needs to be entered as an array Ctrl+Shift+Enter

[@excelisfun]

Data Validation List based on a second data validation list may work. I have a few videos about this. Try these: Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature? Excel Magic Trick 774: Dynamic Data Validation AND Dependent Data Validation Together

[@excelisfun]

Cool! The ROW(INDIRECT("1:"&LEN(cell))) is great, though, I use it in many formulas that have to look at all the individual characters in a cell. Also, when you have a beg date and an end date and need to generate an array of serial numbers the ROW(INDIRECT()) is invaluable...

[@ElvenSorcerer]

You, sir, are an excel god. Thank you for this!!

[@excelisfun]

I am glad that the video helped! Thanks for your support with comment, Thumbs Up and Sub : )

[@JesusFilardo]

Excellent video. I'd like to know if you have one that shows how to get a report from 3 tables that have one item in common like the social security number but watch table has different kind of information. Thank you for sharing your knowledge with everyone.

[@EconosPhylos]

Hey, I´m the number one fan of your videos. Question though. In this case how is this better than using a pivot table?

[@jasonmiller9403]

Great work, very helpful. Thank you sir!

[@excelisfun]

Glad it helped! Thanks for your support with comment, Thumbs Up and Sub : )

[@excelisfun]

Maybe: =LOOKUP(9.99E+307,range/(range not 0),range) Not is the comparative operator less than symbol and then greater than symbol together (KZbin does not allow those characters). or this =LOOKUP(9.99E+307,range/NOT(range=0),range)

[@j.louviere4432]

Thank you for all you do! Your tutorials have really made a difference! One question: I have something similar I am struggling with. I have Lookup Values (PO#) separated by row (no PO# is alike) on one sheet, and need to extract multiple (dates) for each PO# from another worksheet using the same referenced PO#, and display them horizontally in the designated row. Does the same concept apply as you showed above or are there differences? To me, it is a "one to many" ratio on the PO's and extracting those dates from the many shipments. -Hope this makes sense.

[@Squirbie]

You are my new hero! Thank you soooooooooooo much. I knew the answer had to be out there somewhere.

[@yunkkim]

Hi Mike! I have recently discovered you and your videos. I think your great!!! I do not know Excel except for what I have taught myself (which is better than some but by no means intermittent). Should I start from your oldest videos?

[@arielanderson08]

At 3 am I clicked on your video and saw the light... it could be that I am lacking sleep or that I am as hungry as a wildebeest out of hibernation. It could also be that you were sent to answer my most burning question... Can you repeat that? I'm kidding... kind of :-/ This was EXACTLY what I have been looking for for the last week. It's a little beyond my current skill level, however this is how I get better. Thanks!

[@chrisadair8997]

I have a list of 200 spare parts, I want to correlate these with associated tag numbers. I want to show all the data at once. It is a very similar format to what you show in this video, rather than having the separate cell controlling what values are shown, I want to show all values at all times. Is it possible to do this without the count function? Essentially the same results without using F&G 14 in your example.

[@vimal20011]

once again a masterpiece, Mike.... Thanks for posting.

[@NeilsHighLights]

how would I search for more than one item at a time in a worksheet? for example, I'd like to search for any occurrences of "Alpha" and "Beta" in a workbook.

[@rakeshkumar-wo4sz]

Tell me the solution if , lookup value is in same text and the position of value in text is not same very time

[@chrisadair8997]

Is this possible without the count function? I have a list of about 200 part numbers with over 5000 potential matches and I want to display all the information at once.

[@excelisfun]

This does not work: =LOOKUP(1,--(range>0),range) This is about the best one: =LOOKUP(2,1/(range>0),range) I have a video coming out on this in a day or two...

[@muhammadibm4067]

Mike thanks a lot for these great and informative youtubes on excel. Boy, I learned a lot. For three days, I could not figure out how to nest these equations to generate a sum. I do not wand to use the sumif, sumifs or sumproduct alone as the equation in this clip fits exactly my requirement. I wonder if anyone can help?

[@amitkumar-ip6yv]

Mike, awesome trick.nice way to do it. learning a lot and hope to see all your videos.

[@javeed4ioio]

hi i am having one amount in one cell and according to that amount or less then that i hv to debit variuos amount which is in variuos rows, is there any formula that out of that data automaticly highlight the rows according to that one amount, it is vice versa of sum or sumifs

[@insideoutadventure]

I'm a bit of a newbie, but REALLY want to make this formula work. What is the formula you used for cells D14:22? I'm having difficulty finding the tutorial for creating the Relative Position.

[@alkey00]

"B" is the column where where the values is. INDIRECT function translates letter and a row number into range.

[@shantiarichards1012]

Hi! I'm new to this site. I need to be able to perform this task based on a table that is in a different worksheet; as well as have the values displayed in columns that are not side by side! Can you help?

[@JonathanButcher]

Don't know if you are still reading comments, but what about lists with 10k plus entries. This flags as exceeding the boundaries of the formula. Any thoughts?

[@priyalpandya4130]

How can we use one unique ID in one spread sheet (In column A for example) and obtain data multiple row of data with that unique ID from different spreadsheet (all columns)? spreadsheet A has one unique ID- ABC and spreadsheet B have multiple (rows) of records with same unique ID-ABC. I want to copy those multiple records in different tab. unfortunately i can just copy first row of the record and not sure how to obtain all records using this formula.

[@redfeather22sa]

do you have any idea why F9 doesnt work for me (my excel is latest 2016 ms excel). Very frustrating.

[@CodeforceDigital]

omg you saved me! been trying to do this for so long

[@SidathSanjeewa]

Any way to update filtered results and automatically update source data? Anyway thanks for above video. It helped a lot.

[@RyanHoffman76]

Is it possible to have the lookup value "contains" and also have it suggest values as you type in the search box in place of a drop down list that contain 1000's of products?

[@EconosPhylos]

Nevermind, relative position.

[@npf21]

Mike great videos ExcellsFun is awesome make work more enjoyable with your classes. I have a question... so if I want to put the array data all into one cell is it possible? instead of putting each individually and returning each item into a cell horizontally/vertically. if so what excel magic trick should I look at?

[@excelisfun]

I don't know how to do it.

[@rachelb2600]

Hi Mike - Great tutorial as always! Thanks. Quick question on how to build a year over year budget model to show effect of activities from prior year in current year. Would it work if I set it up vertically instead of horizontally?

[@excelisfun]

I am not sure I see how MATCH(ROW(Name),ROW(Name),0) easier than ROW(Name)-ROW(Name)+1… Where did you see this method used to generate an array of relative positions? I would not get in the habit of this method because on larger data sets it would be prohibitively slow in calculating in comparison to the ROW(range)-Row(FirstCellInRange)+1.

[@abbyvarona2676]

This is extremely helpful! Is there a way to display vertically too?

[@willsmith789]

How can we get all the 4 results in one cell as an array ?

[@kuyemthethwa6878]

how does one find HCF( Highest common factor) in excel>

[@ihatevengeance5070]

This video helps me a lot..thanks bro..more power

[@Squirbie]

I don't follow the "B" reference. Please elaborate.

[@westside0606]

This is truly helpful video. Thank you so much for your hard work. Like it and Love it. Thanks

[@coolsomu83]

Hey Mike, This video is gr8 & ExcelIsFun is superb! I had a query, regarding the problem you showed in video, what if you need to extract two field for name and place them horizantally. for example: Date - Data - Name and i want to extract Date & Data for a duplicate name and place them horizantally like below. Name: Joe Date 1 - Data 1 - Date 2- Data 2 ... and so on. Thanks.

[@excelisfun]

here is a video: Excel Magic Trick 606: INDEX & MATCH Two Lookup Values or Excel Magic Trick 398: VLOOKUP with Two Lookup Values (IFERROR function also) 2 lookup values There are other videos also...

[@Chedda_chz]

Mike, great tutorial! I sent you a message for this one. I have data in columns A and B which are city names. City in B is local to A from a telephone. Since one city in A may have multiple B cities, the list is longer and clunky. I want the multiple cities from B listed across horizontally. ANCHORAGE EAGLERIVER ANCHORAGE GIRDWOOD I want: ANCHORAGE EAGLERIVER GIRDWOOD Any ideas? Thanks!

[@excelisfun]

You are welcome!

[@excelisfun]

You are welcome!!