HomeОбразованиеRelated VideosMore From: ExcelIsFun

VLOOKUP To Get Complete Record: EMT 1532 Part 2: COLUMNS & Table Headers or FILTER Function?

385 ratings | 11077 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1532.xlsx In this video see how to use VLOOKUP to retrieve an entire record using two methods: 1) FILTER Function and 2) VLOOKUP, COLUMNS and Table Header Code. 1. (00:05) Introduction 2. (01:16) FILTER Function 3. (03:18) VLOOKUP, COLUMNS and Table Header Columns 4. (07:57) Summary Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) https://www.youtube.com/watch?v=7jJMDGZpjSk
Html code for embedding videos on your blog
Text Comments (100)
ogwal francis (2 months ago)
Microsoft get more and more awesome, Thanks you so so much Mike.Am finding problems working with some features in Kutools and Kutools plus in Excel
ExcelIsFun (2 months ago)
I am sorry, ogwal, I do not know how to use Kutools.
Remy Romano (2 months ago)
How could anyone not like this?
ExcelIsFun (2 months ago)
Right? Thanks for your support, Remy : ) : )
Salim Saba (2 months ago)
That is unbelievably good. What a powerful funcion. I must try it soon.
Salim Saba (2 months ago)
+ExcelIsFun Thanks.
ExcelIsFun (2 months ago)
+Salim Saba Right now, as of Jan 2019, you have to sign up for the "Insider Edition" of Office 365 (google it), but MS says that all versions of Office 365 will have it soon.
Salim Saba (2 months ago)
+ExcelIsFun Mike, I have Microsoft Office 365 on my PC, but I can not find this function in it. Can you please tell me what the reason can be? Thanks in advance and best regards. Salim
ExcelIsFun (2 months ago)
More power to you, Salim!!! : )
Pawan Kumar (2 months ago)
Sequence and Filter function is not appearing. What to do?
Thank you! https://www.youtube.com/playlist?list=PLs7T7QL3y4b-rT5-waGLFnlBHRdQjH2cx People Can also watch my videos; for extracting the data from multiple column using vlookup or INDEX/MATCH formula
Mohamed Chakroun (3 months ago)
Thanks Mike for this tricks, go team :-)
mahmoud kholif (3 months ago)
I typed this =FILTER(dEmp[[First]:[StartSalary]],dEmp[ID]=B10) but every time I receive an error message says That function isn't valid.
Wayne Edmondson (3 months ago)
Hi Mike.. more dynamic array function awesomeness! Thanks for the great video and clever ideas and solutions. Thumbs up!
Andreas Bodemer (3 months ago)
Your enthusiasm gives me life. Stay thug.
ExcelIsFun (3 months ago)
Glad the videos helps with life and Excel!!! Thank you for your support : )
Peter Bartholomew (3 months ago)
Return an entire record minus the first field. At last, a use case where the nations favourite, VLOOKUP, has the edge over INDEX/MATCH! :-) Utterly trivial but the SEQUENCE could be simplified by recognising that the Headers and the Table Body have the same number of columns. I suspect the simplest solution would be to use FILTER on the entire table and not try to embed the query within the returned record/s.
Firas Sunnaá (3 months ago)
Thank you for this great video, may I ask how did you enter {2,3,4,5,6} in the VLOOKUP formula?
Malina C. (3 months ago)
As always EXCELlent video and solution :). Thanks for the film!
ExcelIsFun (3 months ago)
You are welcome, Teammate : )
Alla Ahamed (3 months ago)
Nice video Dear friend I have a question , I want to know how I can send automatic alert email from excel about expiry date
ExcelIsFun (3 months ago)
I do not know. Sorry. Try this great Excel question site: mrexcel.com/forum
Kevin Carter (3 months ago)
The SPILL error got me in one of my tables. I wrapped the Filter function inside the new unique function, and it worked just as I wanted. It got the first record just like Index / Match, or vLookup would have! 👍👍👍
ExcelIsFun (3 months ago)
Yes!!!! Awesome fun with these Dynamic Arrays : ) Thanks for watching, Kevin!!!!
edgie da pogi (3 months ago)
WOW... amazing … I can't say anything.. but amazingly beautiful :) thanks again Mike
ExcelIsFun (3 months ago)
You are welcome! I am glad that it is beautiful for you !!! Thanks for your support, edgie!!!!
Ismail Ismaili (3 months ago)
thank you so much Mr. Mike you are always the best I really appreciate your videos all the time
ExcelIsFun (3 months ago)
You are welcome, Ismail!!!! I appreciate your support : )
Hasan Toma (3 months ago)
Thanx
ExcelIsFun (3 months ago)
You are welcome, Hasan!!! Thank you for your support with your comment, Thumbs Up and Sub : )
John Borg (3 months ago)
Thanks Mike. :) :)
ExcelIsFun (3 months ago)
You are welcome, John!
Mahmoud Salem (3 months ago)
if I want transpose multiple not adjacent rows into one column, can I do that?
ExcelIsFun (3 months ago)
Yes, I think I have a video that shows something close: https://www.youtube.com/watch?v=y64H6NntpLk&t=12s&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=17 But I do not think it would be too hard. In Fact I think we can just use FILTER. Try this video: https://www.youtube.com/watch?v=5zRab2Grz7Q&t=19s&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6
Mahmoud Salem (3 months ago)
if I want transpose multiple in adjacent rows into one column, can I do that?
ExcelIsFun (3 months ago)
Yes, I think I have a video that shows something close: https://www.youtube.com/watch?v=y64H6NntpLk&t=12s&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=17 But I do not think it would be too hard. In Fact I think we can just use FILTER. Try this video: https://www.youtube.com/watch?v=5zRab2Grz7Q&t=19s&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6
ExcelStrategy (3 months ago)
In every video in which is used the new Excel Calc Engine should be Always mentioned that to use it you must bound your bank account to Microsoft for the rest of you life… if it happens that for any accident you can't pay the monthly fee, those features are not reversible and they will not work on a normal version of Excel. Say bye bye to the 100 hours spent building a solution for you or your client. I'm the only one out there who thinks that this microsoft marketing policy it is a complete nonsense ?
ExcelIsFun (3 months ago)
+ExcelStrategy , I do not think we have to attach to Microsoft for eternity, but for now, the Excel and Power BI Data Analysis tools are pretty amazing and useful : )
ExcelStrategy (3 months ago)
I still have heavy doubts about that. I'm not entirely sure that I want to attach myself to Microsoft into eternity.
ExcelIsFun (3 months ago)
It maybe complete nonsense, but the future of all applications from all companies is subscription based in the cloud. And that is what Office 365 is. In a year or two, we will not be able to avoid it. The disatvantage is that we have to pay each month, the advantage is that the version will always be updated, and eventually everyone will always have the correct version.
Maher Nasri (3 months ago)
Thanks, I have question please: is office 365 the only version that has dynamic array functions ? what about office 2019?
ExcelIsFun (3 months ago)
You are welcome, Maher!!! Thank you for your support with your comment, Thumbs Up and Sub : )
ExcelIsFun (3 months ago)
Micrsosoft says Office 2019 does not have Dynamic Arrays. If you can avoid it, do NOT buy Office 2019. It will not get any of the new features that Microsoft will be releasing in the years to come...
aamir khan (3 months ago)
great video sir can we make it dynamic for rows i.e new record
ExcelIsFun (3 months ago)
If we use the Excel Table (like we did), it will be dynamic when you add new rows to the Lookup Table.
ricardoob (3 months ago)
Thank you very much, Mike!!! Great!! It seems like magic!! Regards.
ExcelIsFun (3 months ago)
Thanks to you for the idea : )
ricardoob (3 months ago)
Thank you very much, Mike!!! Great!! It seems magic!! Regards.
ExcelIsFun (3 months ago)
Thanks to you for the idea : ) Thanks for your support, ricardoob!!!!
jayaram vayala (3 months ago)
Thank you for giving videos
ExcelIsFun (3 months ago)
You are welcome for the giving, jayaram!!!! Thank you for your support with your comment, Thumbs Up and Sub : )
Thanks Mike for this EXCELlent video
ExcelIsFun (3 months ago)
You are welcome, Syed!!! Thanks for your EXCELlent Teammate support : )
Vikas Bhalekar Excel (3 months ago)
How we can making searchable drop down list sir,...
Dana Charette (3 months ago)
​ Vikas Bhalekar Excel= just create the dropdown lists using data validation then create a vlookup based off of the data selected in the drop-down.
Esther k! (3 months ago)
+Vikas Bhalekar Excel ok, then the link I sent above is just what you need! It's an Excel Is Fun video and it explains how to create drop down lists. Enjoy!
Vikas Bhalekar Excel (3 months ago)
+Esther k! Yes sir, data validation drop down list..
Esther k! (3 months ago)
+ExcelIsFun I think he means the data validation technique using drop down lists.
Esther k! (3 months ago)
https://youtu.be/2UDRm-hU0Jw Try Mr Girvin's Magic trick 549. That's the link to it.
femaledeer (3 months ago)
Why is Microsoft only making some technology only available in office 365?
ExcelIsFun (3 months ago)
I do not know... But moving forward in history, in a few years all there will be in Office 365, so eventually everyone will have the same version.
SOBHAN SS (3 months ago)
Great & atteractive Mr.Excel,you're fabulous I love learning Excel really well
SOBHAN SS (3 months ago)
+ExcelIsFun My pleasure
ExcelIsFun (3 months ago)
You are welcome, SOBHAN!!! -- excelisfun
DRSteele (3 months ago)
If one were intent on using function FILTER while still accounting for expanding columns to the right of the Excel Table, this construction would do it. In C31 =FILTER(FILTER(dEmpANSWER,dEmpANSWER[ID]=B31),FILTER(dEmpANSWER,dEmpANSWER[ID]=B31)<>B31)
ExcelIsFun (3 months ago)
Very inventive! Thanks for the great Teammate help, DRSteele : )
RRR program (3 months ago)
Brilliant stuff as usual....
ExcelIsFun (3 months ago)
Thanks, RRR!!!! Thanks for the fun support : )
Excel Bear (3 months ago)
excel just keeps getting better, and you just keep showing show awesome it is!
ExcelIsFun (3 months ago)
Yes, Excel just keeps getting more awesome!!! Thanks for your support, Excel Bear!!!
Andrew White (3 months ago)
I don't have a particular use case for this yet, but I can already tell this is going to be super powerful.
ExcelIsFun (3 months ago)
Super powerful, for sure : ) Thanks for the support, Andrew!!!
Matt Schoular (3 months ago)
Thanks Mike.. I cant wait for the Dynamic Array release for Office 365..
ExcelIsFun (3 months ago)
Yes!!! I can't wait for the release either, because then everything we do in Excel will be more fun and efficient : ) Thanks for the support, Matt!
Chris Mancinelli (3 months ago)
That is awesome,. thanks Mike!
ExcelIsFun (3 months ago)
You are welcome, Chris!!! Thanks for your amazing support : )
Sanjeev Soni (3 months ago)
Really amazing Mike, showing these variations will make dynamic array will make more dynamic. Will it be possible for do less than or greater than Match Look ups..
Sanjeev Soni (3 months ago)
Hi Mike here is the lookup table Look up Table Item Amount tt1 10 tt1 20 tt1 20 tt1 40 tt1 110 tt1 1000 tt1 44 tt2 110 tt2 140 tt2 444 tt2 555 tt2 44 Below the list of the value more than amount Item Amount Output- All Amount more than >30 tt1 30 40 44 110 1000 Please advise how to achieve it.
ExcelIsFun (3 months ago)
I am sure that we can do that. But I am not sure what you mean?
imad charabi (3 months ago)
Thanks
ExcelIsFun (3 months ago)
You are welcome, imad!!!! Thank you very much for your support : )
Ash Finkter (3 months ago)
Thanks again. Just wondering... What if I only wanted to spill and return data from only columns 3 and 5? Could I do that somehow with the one formula? If that's possible, could I do it the other way around, so returning columns 5 and then 3? Thanks
Richard Hay (3 months ago)
I have done it done it several times since Dynamic Arrays with FILTER(CHOOSE).
ExcelIsFun (3 months ago)
Thank you for your support with your comments and Thumbs Up : )
ExcelIsFun (3 months ago)
Enter {3,5} into 3rd argument of VLOOKUP : )
Kevin Carter (3 months ago)
Mike, I hadn't considered the fact you could use Filter to replace vLookup, or Index / Match. I just incorporated it in one of my workbooks, and find it very intuitive and easy to learn. Wonderful with Transpose as well. Thank You!!!
ExcelIsFun (3 months ago)
+Kevin Carter @Kevin Carter , I am glad that they are awesomer each time - it is like that for me making them too : )
Kevin Carter (3 months ago)
Your videos get awesomer every time I watch them, and I pick something new out of them EVERY time!!! 😃👍
ExcelIsFun (3 months ago)
What!?!?! I already have two videos about replacing VLOOKUP with FILTER... I guess you missed those. The MAIN reason that FILTER is sooooooooo amazing is that it replaces the very complex formulas for "Return Multiple Records for One Lookup Value" situations that are so common. In fact, for many of the formulas in my Ctrl + Shift + Enter book, FILTER replaces them with, as you say, "very intuitive and easy" formula method. Really. the New Dynamic Arrays will make our Excel life MUCH easier : ) Thanks as always for your support!
Again??. Thanks
+ExcelIsFun Previously, I thought it would take a lot of macro code for the financial management sheet on my farm, I am surprised, this is simple...
ExcelIsFun (3 months ago)
Yes, again!!!!! More detail about a particular topic is good : ) Thanks for the support!!!
Vida (3 months ago)
Thanks for showing how to make dynamic arrays more dynamic
ExcelIsFun (3 months ago)
More dynamic is good : ) Thanks for your support, Vida!!!
Finance in 5 Minutes (3 months ago)
Wow.Thanks as Usual
Finance in 5 Minutes (3 months ago)
ExcelIsFun Thank You very much sir.
ExcelIsFun (3 months ago)
You are welcome, Teammate! Thanks for your support : )
LeFormateurDeGestion (3 months ago)
Thank you !
ExcelIsFun (3 months ago)
You are welcome, LeFormateurDeGestion !!! Thanks for your support : )

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.