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 three methods: 1) COLUMNS, 2) ROWS, 3) SEQUENCE.
1. (00:05) Introduction
2. (00:35) Do we show record horizontally, or vertically? Discussion of sequential numbers that represent the column numbers needed by the VLOOKUP Function.
3. (01:33) COLUMNS & VLOOKUP
4. (03:51) ROWS & VLOOKUP
5. (04:48) SEQUENCE & VLOOKUP (requires that you have Office 365)
6. (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
Hi, Mike. Thank you for your all amazing videos. Just one doubt about this one... With Columns or Rows functions, it is possible to fix the beginning and use them without knowing the last column (just drag)...Drag and delete the blank results... With Sequence, it seems we have to know the quantity of columns prior to writing it in the formula... Is it correct? Regards, Ricardo - Rio de Janeiro - Brazil.
I always used to write the numbers (2-6 in this example) on top of the cells and then used them as a reference FI C$2 (in case I need to copy them down) for the 2 in column C. The SEQUENCE function is fascinating! I can't wait till it and the other dynamic array functions become available.
Hey Mike.. excellent! I've used the old method often to extract whole records.. great review. With the new SEQUENCE() function.. no longer need to "go the the last cell and hit edit to verify the correct ranges".. something you have drilled into my brain via your great Busn216/218 video series.. haha!! Truly amazing and game changing. Thanks for all the master tips and lessons. Thumbs up!
Agreed.. I think only a relative handful of users take advantage of the true power of EXCEL. Most get by day to day with SUM() and maybe IF() and don't realize what they are missing or what they could do with more knowledge. Oh well.. that's an opportunity for those of us who invest the time. Thanks again for all the great instruction. Happy New Year and Thumbs up for 2019!!
You are welcome! Yes, these new Dynamic Arrays will chnage how we can do everything. It will be interesting to see how long it will take to change how people do things. In the 1990s it took about 10 years for the PivotTable to take hold, and even though Power Query was invented in 2013, it still has not taken total hold either...
its excellent Mr.Mike. As i dont have office 365, i prefer (no other way) columns function or even Match will bring the col.index number for vlookup. but Sequence is realy great. Thanks a lot for this video. God bless.
During the time since I posted my original comment to this video I have been trying FILTER(CHOOSE) with this data set. I have worked through several examples and I believe I like FILTER(CHOOSE) the best for this type of setup. I hope you and your family have a Merry Christmas and a Happy New Year and I hope Santa brings the rest of us lots of new ExcelIsFun videos to watch during 2019! Thanks for all your Hard Work!! :)
Sir, I'm facing these two complicated problems. Please help me. 1) Return all the values that match the criteria but with leaving blank ("") every other cells: Sir, can you please make a video on how to return the values with leaving every other cells blank ("")? Suppose there are some sales in November. I have to show all the sales of November, but leaving every other cells as blank ("").
2) 4 ways Lookup but dealing with Merge Cells: Suppose like our Electric Bills, Name of the past 6 months are divided into 4 Columns each. For example, Last 6 months were June, July, August, September, October and November. Under each of these month contains previous year (2017) unit consumption and previous year (2017) billing charges & this year (2018) unit consumption and this year (2018) billing charges. Now, lets assume that there are 5 customers. A, B, C, D and E. Now my question is,- how to Lookup "How much units did customer "D" consumed and the charges that customer "D" had to pay in November of Previous Year (2017) and this year (2018)?"
+edgie da pogi I did. The pdf notes below each MSPTDA video is the free book that I give away to you and the rest of the world : ) I also Have free books below my Excel Basics series, Busn Math series and Advanced Excel series : )
Hi Mike... just a question, Im planning to buy the lifetime license for Office 365... and I am being offered to purchase office 2019 instead... is this the same office 365 that automatically updates to have the NEW Calculation Engine?
Sir Today I have question That I want Formating in the way if I put
123 in A1 cell then B1 cell give me ABC, just like that if i put 546(could be any number) in A2 cell then B2 give me EDF(It could be any thing according to given numeric in A2 cell) ,So on. Sir Want it By Formatting or By formula but short Formula, Pls Sir Make it Possible. Your "Excel Fan".
This is amazing Mike; Most people are still scared to integrate vlookup with rows or columns. Such kind of integration works like team where result of one function becomes input for the other. I however, many times prefer to integrate match with vlookup (using exact column lables); so even when the sequence of the column changes; the vlookup result is accurate because match brings out relative position of data in array. Thanks for upload :)
Hi Mike. Love that SEQUENCE formula. Maybe one day I'll break down and get 365. In the meantime, I guess I will have to rely on old faithful COLUMNS and ROWS or use vba. :) Hope you and your family have a great Holiday. WRH...
Thanks for the holiday wishes, krn14242!!! Happy holdays to you and your family : ) And yes... you MUST get Office 365 because there are just too many amazing features like Dynamic Array Formulas, Power Pivot, TEXTJOIN, Upload Excel to Power Bi and so much more : )
Unfortunately I am not a Microsoft Employee and I am not an expert in Microsoft's Marketing and Sales department. If you are asking about the Insider Program, if you buy Office 365, then go to this site, it will show how to become part of the Insider Program: https://insider.office.com/en-us/join/pc
Because the information about how many columns there are comes from the count of the Field Names. If you were to insert a column between the last and first Field Name, then the formula would update from 5 items to 6 : ) : )
Thank you for the support, Laxmiprasad, with your comment, Thumbs Up and Sub : )
ExcelIsFun yeah! ; I studied English from scratch and I think I'm lucky because when you see people(believe it or not)
pay 200$ and more for a few days about pivot table basics just because they don't understand English!!
they miss all beauty and knowledge about excel for free !!!!
Yes indeed, Old and New makes you a master. I remember about 20 years ago buying old books about VisiCalc and Lotus to learn about the history of spreadsheets. It always helps to know where you caome from so that it helps to plan where you are going : ) Thanks for the support Digital Cooking!
I am sorry but I am not a Microsoft Employee. I have no way of giving you Office 365. Currently, the Dynamic Arrays are only in the Insider Edition of Office 365. Microsoft says that everyone on Office 365 will have the new arrays in a few months, early next year : )
SEQUENCE provides interesting flexibility. In this example, SEQ can be used to transfer headings as well. I created a data set with the same columns as yours but only 5 records. Then used =VLOOKUP(C17,B6:G10,SEQUENCE(,5,2),0) as you did to retrieve complete record plus I used =VLOOKUP(C16,B5:G5,SEQUENCE(,5,2),0) where row 5 contained the headers to also place headers above the record. Then I used =VLOOKUP(C20,B5:G5,SEQUENCE(,1,6),0) and =VLOOKUP(C21,B6:G10,SEQUENCE(,1,6),0) to look up just salary (for example) and also to bring the correct headers for ID and Salary only. Also, since the sequence 3rd argument contains the item to be retrieved, you can put the number in a helper cell and refer both the header and the record functions to it and both header and value will change simultaneously. Further, SEQUENCE (,2,3,2) will return Last Name & Vest Date, etc. Can reconfigure SEQ to retrieve other selected values and have correct headers follow.
I think SEQUENCE is the better solution. I also notice that ROW, ROWS, COLUMN and COLUMNS are functions that do not report an error when they refer to the cell which they occupy. At 4:50
wouldn't this be simpler? =FILTER(dEmp[[First]:[StartSalary]],dEmp[ID]=B20)
Yes, it is funny you should notice, ROWS and COLUMNS and ROW and COLUMN are the are function that don't give circular reference errors. I think it is because the internal code does not look at the content of the cell, just the location : )
ExcelIsFun Yes, of course, I get it. It’s the dynamic array thing that does it, not the ‘dynamic’/expanding range that grows as you copy the formula down or across. (As I type this, I realise I’m just rephrasing what you just wrote.)
I have been watching all the videos you bring out, and it’s been relatively quiet, but I figured it takes a long time to produce such epic videos like the MSPTDA ones. In case you’re wondering: yes, of course, I’m sub’d and the bell is active, so I shouldn’t miss any new videos — hey what did you expect! ;-)
On the other hand: super busy at work, working weekends and all in order to meet deadlines. That’s why I haven’t fully watched the PowerBI video of the MSPTDA, yet. It’s on my backlog.
Thanks for asking and for all your knowledge sharing, I’ll be using it (again) for work in the near future.
Because the COLUMNS and ROWS in VLOOKUP are counting the cells internally as the formula is copied across or down. But SEQUENCE just needs the correct count of columns either in the column argument or the row argument... Haven't heard from you in a while, glad to hear form you!!! You been watching MSPTDA?
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.