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

VLOOKUP To Get Complete Record: ROWS, COLUMNS or SEQUENCE Function? EMT 1532

948 ratings | 20640 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 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
Html code for embedding videos on your blog
Text Comments (184)
ricardoob (3 months ago)
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.
ExcelIsFun (3 months ago)
+ricardoob Yes, I will have to make another video to show this formula: =VLOOKUP(B41,dEmpANSWER,SEQUENCE(,COLUMNS(dEmpANSWER[#Headers])-1,2),0)
ricardoob (3 months ago)
Thank you!! Great!
ExcelIsFun (3 months ago)
This formula will work to accomidate any new columns: =VLOOKUP(B41,dEmpANSWER,SEQUENCE(,COLUMNS(dEmpANSWER[#Headers])-1,2),0)
ExcelIsFun (3 months ago)
With SEQUENCE if we use COLUMN, then If you were to insert a column between the last and first Field Name, then the formula would update from 5 items to 6 - and it would know the quantity of columns.
Kumar Behera (1 month ago)
I am trying in 365, but it's not considering can you suggest
ExcelIsFun (1 month ago)
Currently, as of Feb 16, 2019, it has to be the Office 365 Insider Edition. https://insider.office.com/en-us/
Aaseef khan (1 month ago)
365 is fun
Enny Kraft (1 month ago)
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.
Adarshram Tripathi (1 month ago)
Really Bro, truly amazing............!!!!!!
ExcelIsFun (1 month ago)
Glad it helps, Adarshram!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Vino Dhiliban (2 months ago)
Thanks hike for letting us know such amazing new command,
ExcelIsFun (2 months ago)
You are welcome, Vino!!! Glad the command is amazing for you. Thank you for the support with your comment, Thumbs Up and Sub : )
Wayne Edmondson (2 months ago)
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!
ExcelIsFun (2 months ago)
+Wayne Edmondson , Happy New Year, Wayne!!!!
Wayne Edmondson (2 months ago)
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!!
ExcelIsFun (2 months ago)
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...
Zoran Dimitrijevic (2 months ago)
Thank you very much, can you give me that company name where start salary is 69K, I would like to apply for job.
Manikandan Arunachalam (2 months ago)
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.
ExcelIsFun (2 months ago)
You are welcome, Manikandan!!! Thanks for your support : )
M. SZ. (2 months ago)
VLOOKUP. Always from a new perspective. Thanks.
JOYDEB CHAKRABARTY (3 months ago)
thanks a lot sir....
ExcelIsFun (3 months ago)
You are welcome a lot, JOYDEB!!!! Thank you for your support : )
Chandra Sharma (3 months ago)
How can we download or make active the advance functions like unique, short, filter, sequence in Office 365? Please suggest.
Chandra Sharma (3 months ago)
Thank you boss.
ExcelIsFun (3 months ago)
You have to search Google for Office Insider, then sign up. Otherwise, Office 365 will get an update soon.
Mergen Man (3 months ago)
Only one word about your Chanel! The best!
ExcelIsFun (3 months ago)
Glad it is good for you, Mergen!!!! Thank you for your support : )
Paul Tony (3 months ago)
46 26 34 19 74 95 34 90 4 93 5 70 12 92 21 93 21 69 76 40 62 97 93 74 35 50 29 82 59 56 45 50 24 30 42 48 92 48 30 57 34 9 15 5 81 57 14 33 24 34 4 93 1 75 9 22 50 38 28 80 16 5 5 71 89 63 7 81 16 17 54 5 95 59 15 17 30 53 65 31 80 39 67 57 16 97 28 45 CAN YOU PLEASE TELL ME NEXT FOR DIGIT SIR ???? (BETWEEN 01 - 100 )
We360 (3 months ago)
Amazing Sequence
ExcelIsFun (3 months ago)
Yes it is!!! Glad you like it : ) And thanks for the support, We360!!!
Awesh Bhornya (3 months ago)
As usual awesome videos
ExcelIsFun (3 months ago)
Glad it is awesome for you! Thanks for the support, Awesh!!!
Richard Hay (3 months ago)
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!! :)
ExcelIsFun (3 months ago)
Awesome because there will be lots of excelisfun videos in the new year!!!! Happy Holidays, Richard!!
Malina C. (3 months ago)
SEQUENCE is awesome!!! Love it!
ExcelIsFun (3 months ago)
Glad you love it, Teammate!!!!
pmsocho (3 months ago)
Great tricks!
ExcelIsFun (3 months ago)
Thanks, Teammate!!!!
Attila Juhász (3 months ago)
after highlighting c10:g10 you can also use =vlookup(b10,table,{2,3,4,5,6},0) +hit ALT+Enter where you define an array with the numbers just like with the sequence function
ExcelIsFun (3 months ago)
Great Traditional Array Formula, Attlia!!!!
Kartick Chakraborty (3 months ago)
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)?"
MrsCathT (3 months ago)
Wow!,,,
ExcelIsFun (3 months ago)
Thanks for your support with your WOW comment, Thumbs Up and Sub : )
Raja S (3 months ago)
YouTube should have a button for "Take a bow "...
ExcelIsFun (3 months ago)
Thanks for the cool and kind words, Raja!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Sanjeev Soni (3 months ago)
Amazing Use of Sequence and Column and Idea of using Table Header simplify and will create dynamic vlookup. Looking forward for the next video.
ExcelIsFun (3 months ago)
Glad you like it, Sanjeev!!! Thanks for your support : ) : )
Remy Romano (3 months ago)
How could anyone dislike this video. I love the stuff!
ExcelIsFun (3 months ago)
Thanks for your support, Remy!!!!!
Leila Gharani (3 months ago)
Thank you Mike for another wonderful video :)
ExcelIsFun (3 months ago)
You are welcome, Teammate!!!
edgie da pogi (3 months ago)
Hi Mike, Thank you for another wonderful video and expecting to see more.
excel is fun (2 months ago)
+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 : )
edgie da pogi (2 months ago)
+ExcelIsFun Thanks Mike, as always... much appreciated. Hopefully you can come up with a book regarding Power Query, Power Pivot and the DAX formulas.
ExcelIsFun (2 months ago)
+edgie da pogi MS says that Office 365 is the only one that has the new engine. MS says Office 2019 does not have new engine ...
edgie da pogi (2 months ago)
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?
ExcelIsFun (3 months ago)
Many more to come, Edgie!!! Thanks for the support : )
Wojciech Rybak (3 months ago)
Great solution and formulas !!!
ExcelIsFun (3 months ago)
Glad it is great for you, Thanks for the support : )
Vikas dsem (3 months ago)
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".
ExcelIsFun (3 months ago)
+Vikas dsem It will be today or tomorrow : )
Vikas dsem (3 months ago)
+ExcelIsFun OK Sir , I'm looking forward your Next video Sir 😊😊😊.
ExcelIsFun (3 months ago)
I don't have a formula for that at this time, try asking your question at this great site: mrexcel.com/forum
Ankit Aggarwal (3 months ago)
Suppose we have to look up values from three columns we can use array directly using {2,3,4} in column index number and pressing ctrl shift enter. I use it occasionally.
ExcelIsFun (3 months ago)
Yes, great tip!
anandsharma79 (3 months ago)
Wow. ....vlookup and sequence. ...great formula......thank you so much for all your videos...
ExcelIsFun (3 months ago)
You aer welcome so much! Thanks so much for your support : )
Sachin Vartak (3 months ago)
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 :)
ExcelIsFun (3 months ago)
+Sachin Vartak Yes, for all of us on this Online Excel Team, whenever we see a new dimension or possibility, it makes it much more fun!!!!!
Sachin Vartak (3 months ago)
+ExcelIsFun I'm talking about vlookup and match. Its always good learning to watch your videos. Each of your video opens up a new dimension or possibility . Thanks :)
ExcelIsFun (3 months ago)
Yes, it is more robust to use INDEX and MATCH, but for retrieving the record, the VLOOKUP method is good : )
John Borg (3 months ago)
Thanks Mike. At the moment, I can only practice on the old method (:
ExcelIsFun (3 months ago)
Soon, soon : )
nimrodzik1 (3 months ago)
Dynamic arrays rock. And you rock man :)
ExcelIsFun (3 months ago)
Glad it all rocks for you, nimrodzik1!!! Thanks for your support : )
krn14242 (3 months ago)
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...
krn14242 (3 months ago)
+ExcelIsFun Maybe Santa will surprise me. lol
ExcelIsFun (3 months ago)
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 : )
Seshnath Iyer (3 months ago)
Copy it till the side means Ctrl D?
ExcelIsFun (3 months ago)
Ctrl + D is Fill Down (top cell down in the highlighted ranges).
Yet another amazing video by Mike the awesome ... Thanks Mike
ExcelIsFun (3 months ago)
Your are welcome, Syed!!!!!
gryphon50 (3 months ago)
brilliant!!!! thank you.
ExcelIsFun (3 months ago)
You are welcome, gyphon50!!! Thanks for the support with your comment, thumbs up and Sub : )
Mohamed Chakroun (3 months ago)
As you said, totally amazing Mike :-)
ExcelIsFun (3 months ago)
Glad it was amazing!! Thanks for your consistent support : )
Nadir Ali (3 months ago)
Salute!
ExcelIsFun (3 months ago)
Thanks for the salute : ) Thanks for your support : )
Vida (3 months ago)
So neat solution. Thanks
ExcelIsFun (3 months ago)
You are welcome, Vida!!! Thanks for your consistent support : )
alisalih (3 months ago)
thank you very much ....very helpful..can we get the second and third record and so on with vlookup
ExcelIsFun (3 months ago)
Sure, but you will need to list the lookup values in separate cells and then copy the formula down.
Darkslide820 (3 months ago)
I got your CTRL+SHFT+ENT DVD for Christmas in our Secret Santa exchange!
ExcelIsFun (3 months ago)
Cool!!!! : ) It should be fun for you!!! Thanks for the support, DarkSlide820!!!
Ismail Ismaili (3 months ago)
Thank u Mr. Mike please I want to purchase Microsoft 365 latest version could u please send me the link because I tried with some links but it doesn't work with me
ExcelIsFun (3 months ago)
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
DB LP (3 months ago)
Superb! But why you’ve used columns inside sequence if the sequence itself gives the result
ExcelIsFun (3 months ago)
+DB LP No, just lots and lots of practice and because it is so much fun : )
DB LP (3 months ago)
ExcelIsFun seems you have ms excel chip inside your brain 😅
ExcelIsFun (3 months ago)
+DB LP , this formula will update no matter where we add a new column: =VLOOKUP(B41,dEmpANSWER,SEQUENCE(,COLUMNS(dEmpANSWER[#Headers])-1,2),0)
DB LP (3 months ago)
Brilliant 🙏
ExcelIsFun (3 months ago)
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 : )
Chris Mancinelli (3 months ago)
Great video Mike, Sequence is awesome!
ExcelIsFun (3 months ago)
Thanks for the SEQUENTIALLY awesome support, Chris : ) : )
Hussein Korish (3 months ago)
Just Wonderful .... Thanks Mike
ExcelIsFun (3 months ago)
Glad it is wonderfulf or you!!!! Thanks for the support, Hussein : )
Tulsidas Jamnani (3 months ago)
Mike sir, I found that we can also do this .... With = index(match) function....
ExcelIsFun (3 months ago)
Yes, indeed! Anything VLOOKUP can do, INDEX and MATCH can do too : ) Thanks for the support, Tulsidas!!!!!
Majd Yazigi (3 months ago)
everything you do is amazing
ExcelIsFun (3 months ago)
Glad it is amazing for you!!! Thank you for support, Majd, with your comments, Thumbs Ups and Sub : )
Shameem (3 months ago)
Being a QuickBook Accountant I need excel all the time. And videos are helping me a lot
ExcelIsFun (3 months ago)
Glad the videos help! I use Quickbooks often too, love it! Nad to have both QuickBooks and Excel, now that is a good combo! Thank you for the support, Shameem, with your comment, Thumbs Up and Sub : )
Molnify (3 months ago)
Wow, very cool! Thanks for sharing!
ExcelIsFun (3 months ago)
You are welcome for the share, Molnify!!! Thank you for the support with your comment, Thumbs Up and Sub : )
DIGITAL COOKING (3 months ago)
yes, you right totally amazing!!! plus giving old and new technics let us become veteran in excel ******* :)
ExcelIsFun (3 months ago)
+DIGITAL COOKING , Yes, free Excel is better than $200 Excel : )
DIGITAL COOKING (3 months ago)
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 !!!!
ExcelIsFun (3 months ago)
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!
Atiq Sarwar (3 months ago)
Sir can you send me office 365 download link, I downloaded many time but dynamic arary functions are not working...
ExcelIsFun (3 months ago)
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 : )
Sushant Joshi (3 months ago)
As always.. just amazing.. Hats off to you sir .
ExcelIsFun (3 months ago)
Glad it was hats off amazing for you, Sushant! Thank you for the support with your comment, Thumbs Up and Sub : )
THE REALITY-vs-BAKWAAS (3 months ago)
U r awesome man
ExcelIsFun (3 months ago)
Glad the videos are awesome for you, Spread Ness!!! Thank you for support with your comment, Thumbs Up and Sub : )
angelokimi (3 months ago)
Wow! this will totally solve the issue I face everyday in my reports! Thanks a bunch!! 😊😊
ExcelIsFun (3 months ago)
You aer welcome a buch! Thanks for your support a bunch : )
My Friend (3 months ago)
Hi Do you have any Idea when these new spill function like "Filter" "Sequence" will release to Normal Office 365 users ?
ExcelIsFun (3 months ago)
+Fabio Gambaro That is such good news that Mac has these arrays, but the bad news from Microsoft is that the Mac has no plan so far to bring Power Pivot into the Mac... : (
Fabio Gambaro (3 months ago)
+ExcelIsFun I was also very surprised when I entered an array formula a few days ago and forgot to use CSE, and the formula spilt magically to the nearest cells...
ExcelIsFun (3 months ago)
+Fabio Gambaro I am very surprised... Since the Mac does not have Power Pivot and Power QUery yet...
Fabio Gambaro (3 months ago)
I was surprised to realize that the "SEQUENCE" functions and other array functions are available also in the Mac version of Excel (V16 + Office 365)... what a nice Christmas surprise! :-)
ExcelIsFun (3 months ago)
+My Friend You are welcome!! What a cool YouTube name you have: My Friend : )
Tulsidas Jamnani (3 months ago)
Gr8
ExcelIsFun (3 months ago)
Glad it is great for you,!!! Thanks for the support : )
jc fel (3 months ago)
@5:23 when creating the Sequence formula I notice you put a #2 as the start of the columns but it was counting from column C which is 3rd column, can you expand a bit on this please.
jc fel (3 months ago)
+ExcelIsFun thanks for clearing that up Mike, really enjoy your videos.
ExcelIsFun (3 months ago)
2 is the second column in the table, not the spreadsheet.
Amit Tiwari (3 months ago)
Sequence easier to use. Wht about the speed is this new formula will increase speed or slow down?
ExcelIsFun (3 months ago)
That I do not know. Time to test : )
Khan Abdus Sabur (3 months ago)
Excellent dear sir, Thanks a lot.
ExcelIsFun (3 months ago)
You are welcome for the EXCELlence : )
Excel Bear (3 months ago)
office 365 seems really worth it!
ExcelIsFun (3 months ago)
REALLY worth it: Dynamic Arrays, Power Pivot, TEXTJOIN and so much more : )
Ann Littlejohn (3 months ago)
Sequence looks amazing! Can't wait to apply this.
ExcelIsFun (3 months ago)
Yes, SEQUENCE is great! Thanks for your support, Ann!
Richard Hay (3 months ago)
SEQUENCE has many great uses and this is certainly one of them.
Richard Hay (3 months ago)
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.
ExcelIsFun (3 months ago)
Yes, indeed, SEQUENCE has soo many great uses!
DRSteele (3 months ago)
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)
ExcelIsFun (3 months ago)
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 (3 months ago)
Yes, perhaps FILTER is the better lookup function : )
enrique dominguez (3 months ago)
Easy to understand and apply, Mike. Great explanation as always. Thank you.
ExcelIsFun (3 months ago)
Glad it is easy and fun for you, enrique!!! Thanks for your support : )
Noah Hadro (3 months ago)
When will dynamic arrays be generally available?
ExcelIsFun (3 months ago)
MS says early next year. I hope soon!!!!
Sevag Barsoumian (3 months ago)
Thanks Mike, with SEQUENCE is awesome..!!!
ExcelIsFun (3 months ago)
You are welcome, Sevag!!! Thanks for your support : )
Jose Betancourt (3 months ago)
Hello Mike, thank for the video! 👍
ExcelIsFun (3 months ago)
Hello, Jose!!!! You are welcome for the video. Thanks for your support with your comment, Thumbs Up and Sub : )
Jahanzeb Aslam (3 months ago)
Awesome. Due to you my excel skills are now awesome
ExcelIsFun (3 months ago)
Glad that the video and your Excel skills are awesome, Aslam!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Geert Delmulle (3 months ago)
How come you don’t have to use ROWS() in the sequence function? I did not expect that...
ExcelIsFun (3 months ago)
+Geert Delmulle It is great to be on a fun and amazing Online Excel & Power BI Team with you, Geert!!!!
Geert Delmulle (3 months ago)
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.
ExcelIsFun (3 months ago)
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?
Finance in 5 Minutes (3 months ago)
Loan Amortization with variable Rate can we use Sequence Function to get the Different Loan Rate? Instead of Count function as You showed in that video way back
ExcelIsFun (3 months ago)
Yes, there are so many ways we can use SEQUENCE : )
Finance in 5 Minutes (3 months ago)
Simply Amazing
ExcelIsFun (3 months ago)
Glad it is amazing for you! Thanks for your support : )
Kevin Carter (3 months ago)
Combining old and new. Simply AMAZING! Thank you Professor!!! 👍👍👍
ExcelIsFun (3 months ago)
You are welcome, Kevin!!! Old and New : )
Thank, it very useful
ExcelIsFun (3 months ago)
Youa er welcome for the useful video : ) Thanks for your support with your comment, Thumbs Up and Sub : )
K B (3 months ago)
Great tip, Thanks, Mike... I think I will go with sequence function.
ExcelIsFun (3 months ago)
Glad it was great! Thanks for the comment about SEQUENCE : )
muhammadali jawed (3 months ago)
Very nice
ExcelIsFun (3 months ago)
Glad it is nice for you, jawed!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Loki Jimmy (3 months ago)
This has got me at the right time, Thank you for coming into my rescue, What a gift for Christmas to me from ExelIsFun!
ExcelIsFun (3 months ago)
You are welcome, Loki!!!! Thanks for your support with your comment, Thumbs Up and Sub : )

Would you like to comment?

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