VLOOKUP To Get Complete Record: EMT 1532 Part 2: COLUMNS & Table Headers or FILTER Function?
HTML-код
- Опубликовано: 15 сен 2024
- Download Excel File: people.highlin...
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: people.highline...
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) • Comprehensive Excel Dy...
excel just keeps getting better, and you just keep showing show awesome it is!
Yes, Excel just keeps getting more awesome!!! Thanks for your support, Excel Bear!!!
thank you so much Mr. Mike you are always the best I really appreciate your videos all the time
You are welcome, Ismail!!!! I appreciate your support : )
Thanks for showing how to make dynamic arrays more dynamic
More dynamic is good : ) Thanks for your support, Vida!!!
I don't have a particular use case for this yet, but I can already tell this is going to be super powerful.
Super powerful, for sure : ) Thanks for the support, Andrew!!!
Your enthusiasm gives me life.
Stay thug.
Glad the videos helps with life and Excel!!! Thank you for your support : )
Great & atteractive
Mr.Excel,you're fabulous
I love learning Excel really well
You are welcome, SOBHAN!!! -- excelisfun
@@excelisfun My pleasure
Thanks Mike for this EXCELlent video
You are welcome, Syed!!! Thanks for your EXCELlent Teammate support : )
As always EXCELlent video and solution :). Thanks for the film!
You are welcome, Teammate : )
Hi Mike.. more dynamic array function awesomeness! Thanks for the great video and clever ideas and solutions. Thumbs up!
WOW... amazing … I can't say anything.. but amazingly beautiful :) thanks again Mike
You are welcome! I am glad that it is beautiful for you !!! Thanks for your support, edgie!!!!
Very nice video 📹 Mike :)
Old school and newer school : )
Brilliant stuff as usual....
Thanks, RRR!!!! Thanks for the fun support : )
That is unbelievably good. What a powerful funcion. I must try it soon.
More power to you, Salim!!! : )
@@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
@@sasavienne 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.
@@excelisfun Thanks.
Thank you very much, Mike!!! Great!! It seems like magic!! Regards.
Thanks to you for the idea : )
That is awesome,. thanks Mike!
You are welcome, Chris!!! Thanks for your amazing support : )
Thanks Mike.. I cant wait for the Dynamic Array release for Office 365..
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!
Thank you !
You are welcome, LeFormateurDeGestion
!!! Thanks for your support : )
Thank you for giving videos
You are welcome for the giving, jayaram!!!! Thank you for your support with your comment, Thumbs Up and Sub : )
Thanks Mike, amazing video. Just testing, i found what I think may be another way, but by using Index & Match with Sequence & Column functions.
For row record => =INDEX(dEmp;MATCH(B40;dEmp[ID];0);1+SEQUENCE(;COLUMNS(dEmp[#Headers])-1))
For Column record => =INDEX(dEmp;MATCH(C42;dEmp[ID];0);1+SEQUENCE(COLUMNS(dEmp[#Headers])-1))
Thanks for the formulas, Giovanni : )
How could anyone not like this?
Right? Thanks for your support, Remy : ) : )
Thanks Mike for this tricks, go team :-)
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
I am sorry, ogwal, I do not know how to use Kutools.
❤❤❤❤❤. I am a very Happy.
Thanks Mike. :) :)
You are welcome, John!
Thanks
You are welcome, imad!!!! Thank you very much for your support : )
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
Enter {3,5} into 3rd argument of VLOOKUP : )
Thank you for your support with your comments and Thumbs Up : )
I have done it done it several times since Dynamic Arrays with FILTER(CHOOSE).
Thank you for this great video, may I ask how did you enter {2,3,4,5,6} in the VLOOKUP formula?
Thanx
You are welcome, Hasan!!! Thank you for your support with your comment, Thumbs Up and Sub : )
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..
I am sure that we can do that. But I am not sure what you mean?
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.
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.
great video sir
can we make it dynamic for rows i.e new record
If we use the Excel Table (like we did), it will be dynamic when you add new rows to the Lookup Table.
but if in filter you would select all the data range, then it would be picked up. youwould have to be simply filtered out the unwanted column name.
Sequence and Filter function is not appearing. What to do?
Thanks, I have question please: is office 365 the only version that has dynamic array functions ? what about office 2019?
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...
You are welcome, Maher!!! Thank you for your support with your comment, Thumbs Up and Sub : )
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)
Very inventive! Thanks for the great Teammate help, DRSteele : )
I typed this =FILTER(dEmp[[First]:[StartSalary]],dEmp[ID]=B10) but every time I receive an error message says That function isn't valid.
Wow.Thanks as Usual
You are welcome, Teammate! Thanks for your support : )
ExcelIsFun Thank You very much sir.
Nice video Dear friend I have a question , I want to know how I can send automatic alert email from excel about expiry date
I do not know. Sorry. Try this great Excel question site: mrexcel.com/forum
if I want transpose multiple in adjacent rows into one column, can I do that?
Yes, I think I have a video that shows something close: ruclips.net/video/y64H6NntpLk/видео.html&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: ruclips.net/video/5zRab2Grz7Q/видео.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6
Why is Microsoft only making some technology only available in office 365?
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.
Again??. Thanks
Yes, again!!!!! More detail about a particular topic is good : ) Thanks for the support!!!
@@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...
How we can making searchable drop down list sir,...
I am not sure. Maybe VBA? Try asking here: mrexcel.com/forum
ruclips.net/video/2UDRm-hU0Jw/видео.html
Try Mr Girvin's Magic trick 549. That's the link to it.
@@excelisfun I think he means the data validation technique using drop down lists.
@@estherk7682 Yes sir, data validation drop down list..
@@SanviPatil2019 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!
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 ?
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.
I still have heavy doubts about that. I'm not entirely sure that I want to attach myself to Microsoft into eternity.
@@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 : )
Thank you very much, Mike!!! Great!! It seems magic!! Regards.
Thanks to you for the idea : ) Thanks for your support, ricardoob!!!!
if I want transpose multiple not adjacent rows into one column, can I do that?
Yes, I think I have a video that shows something close: ruclips.net/video/y64H6NntpLk/видео.html&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: ruclips.net/video/5zRab2Grz7Q/видео.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6