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.
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.
you are my savior. 2 days of watching videos and reading suggestions online. You are the only one who explained this well enough for me to understand. Thanks for video. subbing right here
I have been looking for this answer for two days!!!! How to copy down rows without having to hard code them!!! Thank you so much for this vid!!! Will be watching it again and again!!!
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.
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!! :)
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 : )
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!
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...
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!!
Excellent work. I like that good all column and row functions. This new spilling automation is annoying, specially when we have situations to not spill cells. Can you please tell me how to turn off/stop that spilling? And yes, for me, ExcelIsFun with those old formulas.
Hey Mike, great content and thank you for helping so many people along, I just have one question and it’s come up a few times in the comments I just haven’t seen an answer that helps me out, how can you retrieve multiple rows of information with the same account number? many thanks
If you have Microsoft 365 Excel: ua-cam.com/video/XzNfSZBohXc/v-deo.html If you do not have Microsoft 365 Excel it is much harder, but has a well known solution. The link at the end of the above video shows the old method.
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 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 :)
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.
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!
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 !!!!
Hi Mike, Very clear explanation. Thanks. I have one question, If I am doing a look up for more than one value, and if that look up value has more than one row associated with it then how do we do that?
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 Thanks Mike, as always... much appreciated. Hopefully you can come up with a book regarding Power Query, Power Pivot and the DAX formulas.
@@edgiedapogi4848 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. 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 : )
Hi, Mike I had benefit of your videos, I am new learner of Excel so, am wondering bout using VL as shown in this video but to return multi match, both ways Vertical and horizontal for ex. if Tyrone is a salesman and has multiple invoices with different dates and the same ID during a month. Can it be done? because I can get only the first record. Million thanks in advance. Mohammed El-Guindi
Amazing Video. I however have one query... how do you return multiple rows for the same value... ie. if I have a stock price table which gives me signals like buy or sell, how can use the vlookup/columns to return the entire row for value "buy" or "sell"
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
This is awesome, thank you. I've tried this with the FILTER & XLOOKUP functions and they also work well...buy why doesn't the INDIRECT fx work here? I created named ranges using the "Name From Selection" button B$:G8 but INDIRECT(B10) only brings back a zero. A little confused here. Thanks!
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 : )
Is there a way to do it with indirect? like some one write on a referenced cell E10:F11, so whatever is on that range, it have to return it on single column, so later it can be referenced for a dynamic drop down list on data validation
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
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! :-)
@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.
Hi Mike, This is great! I was wondering if we can do the same thing ==return complete record===using two or more lookup values without concatenation or using helper columns? Can XLOOKUP help here?
I don’t why… I did everything exactly as you did it, using Vlookup combined with column, as well as sequence .. it brought the value of the first column but not the rest, when I used the Vlookup and Column but it didn’t work entirely for sequence 😢😢
thank you bro, it's amazing but why it doesnt work at my spreadsheet? i tried many times and duplicate your code but doesn't work. is there any thing to fix this?
We have 1 more option for this, select 4 cells and type =VLOOKUP(lookup_value, array,{2,3,4,5},0) and ctrl+enter. We can put whtever columns we want like{4,5},. Even we can put {5,2}, Note: whatever no. Of columns we want we need to select that much cells first in that case
what if my ID has multiple entries. Let's say I have a customer (ID) with multiple quotes, and the point is to look at those two exact instances and get my data?
Hello I thought your video was interesting, maybe you can help me further, I have a column with an ID in the Excel sheet (EA3: AE22) in addition to the names (there are 20), in addition I have 3 columns, in cell AE2 I also have an Id the column next to it you will also find the name (that changed) that I want to look for in A3: CV21, (each name has 3 columns of data apart from the name), I want to retrieve that data and place it in the right place, Is that possible? For example, in AE2 the name Malacor comes that we find in B2, Now I want to retrieve the data from that column that is in column C and place it with the name (in our case, B2 can be found) and also find in EB2, the data comes on EC4, if you need more info ask, thank you?
What if I have multiple line items matching the cell value I need to look up --> cell value is invoice number, and several lines follow with the same invoice number, but different lines of the invoice. I need to pull all the lines for several invoice numbers from a huge file containing MANY invoice numbers. This formula only pulls one line matching the invoice number. How do I pull ANY lines w/ the matching invoice numbers? THANK YOU!!
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
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: insider.office.com/en-us/join/pc
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)?"
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.
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.
This formula will work to accomidate any new columns: =VLOOKUP(B41,dEmpANSWER,SEQUENCE(,COLUMNS(dEmpANSWER[#Headers])-1,2),0)
Thank you!! Great!
@@ricardoob Yes, I will have to make another video to show this formula: =VLOOKUP(B41,dEmpANSWER,SEQUENCE(,COLUMNS(dEmpANSWER[#Headers])-1,2),0)
You make it very understandable, thank you very much
Easy to understand and apply, Mike. Great explanation as always. Thank you.
Glad it is easy and fun for you, enrique!!! Thanks for your support : )
you are my savior. 2 days of watching videos and reading suggestions online. You are the only one who explained this well enough for me to understand. Thanks for video. subbing right here
That is my specialty: making the complicated, less complicated! Thanks for stopping by in the comments : )
Thank you Mike for another wonderful video :)
You are welcome, Teammate!!!
how to use the formula if the lookup value is error or not found data should remain same as it exists
I subscribed with the speed of sound... Guy knows excel in and out
Thanks for you speed of sound sub. Here is a two minute video about all the free content at my UA-cam Channel: ua-cam.com/video/l1-1aVgFth4/v-deo.html
Wow! this will totally solve the issue I face everyday in my reports!
Thanks a bunch!! 😊😊
You aer welcome a buch! Thanks for your support a bunch : )
I have been looking for this answer for two days!!!! How to copy down rows without having to hard code them!!! Thank you so much for this vid!!! Will be watching it again and again!!!
Glad this helps!
Only one word about your Chanel! The best!
Glad it is good for you, Mergen!!!! Thank you for your support : )
SEQUENCE has many great uses and this is certainly one of them.
Yes, indeed, SEQUENCE has soo many great uses!
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.
VLOOKUP. Always from a new perspective. Thanks.
Amazing Use of Sequence and Column and Idea of using Table Header simplify and will create dynamic vlookup. Looking forward for the next video.
Glad you like it, Sanjeev!!! Thanks for your support : ) : )
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!! :)
Awesome because there will be lots of excelisfun videos in the new year!!!! Happy Holidays, Richard!!
Being a QuickBook Accountant I need excel all the time. And videos are helping me a lot
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 : )
Awesome. Due to you my excel skills are now awesome
Glad that the video and your Excel skills are awesome, Aslam!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
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.
Thank you for this amazing videos Mr. Mike Irvin !!
You are welcome!!!
Sequence looks amazing! Can't wait to apply this.
Yes, SEQUENCE is great! Thanks for your support, Ann!
Another Amazing video! Thanks Mike!
Thanks Mike,
with SEQUENCE is awesome..!!!
You are welcome, Sevag!!! Thanks for your support : )
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!
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...
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!!
@@wayneedmondson1065 , Happy New Year, Wayne!!!!
Hello Mike! Thank you for these great contents.
You are welcome!
Great tip, Thanks, Mike... I think I will go with sequence function.
Glad it was great! Thanks for the comment about SEQUENCE : )
Wow. ....vlookup and sequence. ...great formula......thank you so much for all your videos...
You aer welcome so much! Thanks so much for your support : )
Awesome!!! 😋😋😋😋. the New function sequence with columns and rows functions that is fun.
sequenceisfun : )
Excellent work.
I like that good all column and row functions. This new spilling automation is annoying, specially when we have situations to not spill cells.
Can you please tell me how to turn off/stop that spilling?
And yes, for me, ExcelIsFun with those old formulas.
Great solution and formulas !!!
Glad it is great for you, Thanks for the support : )
Hey Mike, great content and thank you for helping so many people along, I just have one question and it’s come up a few times in the comments I just haven’t seen an answer that helps me out, how can you retrieve multiple rows of information with the same account number? many thanks
If you have Microsoft 365 Excel: ua-cam.com/video/XzNfSZBohXc/v-deo.html
If you do not have Microsoft 365 Excel it is much harder, but has a well known solution. The link at the end of the above video shows the old method.
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 :)
Yes, it is more robust to use INDEX and MATCH, but for retrieving the record, the VLOOKUP method is good : )
@@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 :)
@@sachinrv1 Yes, for all of us on this Online Excel Team, whenever we see a new dimension or possibility, it makes it much more fun!!!!!
Thanks!
Thank you so much for the donation, Byron!!!! : ) : )
How could anyone dislike this video. I love the stuff!
Thanks for your support, Remy!!!!!
So neat solution. Thanks
You are welcome, Vida!!! Thanks for your consistent support : )
Yet another amazing video by Mike the awesome ... Thanks Mike
Your are welcome, Syed!!!!!
Great video Mike, Sequence is awesome!
Thanks for the SEQUENTIALLY awesome support, Chris : ) : )
As always.. just amazing.. Hats off to you sir .
Glad it was hats off amazing for you, Sushant! Thank you for the support with your comment, Thumbs Up and Sub : )
SEQUENCE is awesome!!! Love it!
Glad you love it, Teammate!!!!
office 365 seems really worth it!
REALLY worth it: Dynamic Arrays, Power Pivot, TEXTJOIN and so much more : )
Just Wonderful .... Thanks Mike
Glad it is wonderfulf or you!!!! Thanks for the support, Hussein : )
everything you do is amazing
Glad it is amazing for you!!! Thank you for support, Majd, with your comments, Thumbs Ups and Sub : )
I got your CTRL+SHFT+ENT DVD for Christmas in our Secret Santa exchange!
Cool!!!! : ) It should be fun for you!!! Thanks for the support, DarkSlide820!!!
Hello Mike, thank for the video! 👍
Hello, Jose!!!! You are welcome for the video. Thanks for your support with your comment, Thumbs Up and Sub : )
Excellent Mike thank you.
You are welcome, N Sanch01!!!
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.
Yes, great tip!
yes, you right totally amazing!!!
plus giving old and new technics let us become veteran in excel ******* :)
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!
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 !!!!
@@DIGITAL_COOKING , Yes, free Excel is better than $200 Excel : )
Great tricks!
Thanks, Teammate!!!!
Hi Mike, Very clear explanation. Thanks.
I have one question, If I am doing a look up for more than one value, and if that look up value has more than one row associated with it then how do we do that?
Have you found the answer?
Dynamic arrays rock. And you rock man :)
Glad it all rocks for you, nimrodzik1!!! Thanks for your support : )
Thanks you so much you save too much time of mine. It's really amazing.
I am glad that the video helps, Asim!!!
Hi Mike, Thank you for another wonderful video and expecting to see more.
Many more to come, Edgie!!! Thanks for the support : )
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?
@@edgiedapogi4848 MS says that Office 365 is the only one that has the new engine. MS says Office 2019 does not have new engine ...
@@excelisfun Thanks Mike, as always... much appreciated. Hopefully you can come up with a book regarding Power Query, Power Pivot and the DAX formulas.
@@edgiedapogi4848 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! Thanks for this great video!
My question is: How to get the details when we do vlookup to another tab or another excel file?
Thanks in advance!
Excellent dear sir, Thanks a lot.
You are welcome for the EXCELlence : )
Excellent video!
Glad it is EXCELlent for you, Joshua : )
Hi Mike great video. I'm struggling to apply this to multiple table arrays. Any thoughts? Thanks!
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 : )
@@excelisfun Maybe Santa will surprise me. lol
Hi, Mike
I had benefit of your videos, I am new learner of Excel so, am wondering bout using VL as shown in this video but to return multi match, both ways Vertical and horizontal
for ex. if Tyrone is a salesman and has multiple invoices with different dates and the same ID during a month. Can it be done? because I can get only the first record.
Million thanks in advance.
Mohammed El-Guindi
Thanks hike for letting us know such amazing new command,
You are welcome, Vino!!! Glad the command is amazing for you. Thank you for the support with your comment, Thumbs Up and Sub : )
Hi sir, thanks for wonderful tricks I need columns data from another sheet how I use this function.
Amazing 👏 Mike
Amazing Video. I however have one query... how do you return multiple rows for the same value... ie. if I have a stock price table which gives me signals like buy or sell, how can use the vlookup/columns to return the entire row for value "buy" or "sell"
Wow, very cool! Thanks for sharing!
You are welcome for the share, Molnify!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Excellent Boss
Simply Amazing
Glad it is amazing for you! Thanks for your support : )
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
Great Traditional Array Formula, Attlia!!!!
This is awesome, thank you. I've tried this with the FILTER & XLOOKUP functions and they also work well...buy why doesn't the INDIRECT fx work here? I created named ranges using the "Name From Selection" button B$:G8 but INDIRECT(B10) only brings back a zero. A little confused here. Thanks!
Amazing Sequence
Yes it is!!! Glad you like it : ) And thanks for the support, We360!!!
As usual awesome videos
Glad it is awesome for you! Thanks for the support, Awesh!!!
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, perhaps FILTER is the better lookup function : )
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 : )
brilliant!!!!
thank you.
You are welcome, gyphon50!!! Thanks for the support with your comment, thumbs up and Sub : )
Really Bro, truly amazing............!!!!!!
Glad it helps, Adarshram!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Amazing man, it works
Glad it helps!
Hello, is xlookup have this function as well? I mean can i combined to column function? Thank you 😊
Is there a way to do it with indirect? like some one write on a referenced cell E10:F11, so whatever is on that range, it have to return it on single column, so later it can be referenced for a dynamic drop down list on data validation
thank you very much ....very helpful..can we get the second and third record and so on with vlookup
Sure, but you will need to list the lookup values in separate cells and then copy the formula down.
Sequence easier to use.
Wht about the speed is this new formula will increase speed or slow down?
That I do not know. Time to test : )
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
Yes, there are so many ways we can use SEQUENCE : )
Thank, it very useful
Youa er welcome for the useful video : ) Thanks for your support with your comment, Thumbs Up and Sub : )
As you said, totally amazing Mike :-)
Glad it was amazing!! Thanks for your consistent support : )
Hi
Do you have any Idea when these new spill function like "Filter" "Sequence" will release to Normal Office 365 users ?
MS says early next year. So hopefully in a few months : )
@@excelisfun Thank you for reply 😊
@@Anonymous-le2zr You are welcome!! What a cool UA-cam name you have: My Friend : )
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! :-)
@@FabioGambaro I am very surprised... Since the Mac does not have Power Pivot and Power QUery yet...
@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.
2 is the second column in the table, not the spreadsheet.
@@excelisfun thanks for clearing that up Mike, really enjoy your videos.
Hi Mike, This is great! I was wondering if we can do the same thing ==return complete record===using two or more lookup values without concatenation or using helper columns? Can XLOOKUP help here?
Yes, but you need to join (concatenate), as with: =XLOOKUP(I8&J8,fEmployee[State]&fEmployee[Store],fEmployee)
@@excelisfun Thanks much!
Thanks Mike. At the moment, I can only practice on the old method (:
Soon, soon : )
I don’t why… I did everything exactly as you did it, using Vlookup combined with column, as well as sequence .. it brought the value of the first column but not the rest, when I used the Vlookup and Column but it didn’t work entirely for sequence 😢😢
thank you bro, it's amazing but why it doesnt work at my spreadsheet? i tried many times and duplicate your code but doesn't work. is there any thing to fix this?
Kindly clarify, how to return entire records if Similar ID repeats twice in same coloum
We have 1 more option for this, select 4 cells and type =VLOOKUP(lookup_value, array,{2,3,4,5},0) and ctrl+enter. We can put whtever columns we want like{4,5},. Even we can put {5,2},
Note: whatever no. Of columns we want we need to select that much cells first in that case
Very nice!!! : )
Thanks for your contributions and support, Pravin !
GOAT STATUS
Glad this helps, Nyzaire!!!!
What if the data given is in Row not in Column like that your given example sir? can you please show tutorial bout it? thanks in advance.
Salute!
Thanks for the salute : ) Thanks for your support : )
Very nice
Glad it is nice for you, jawed!!! Thanks for your support with your comment, Thumbs Up and Sub : )
what if my ID has multiple entries. Let's say I have a customer (ID) with multiple quotes, and the point is to look at those two exact instances and get my data?
When will dynamic arrays be generally available?
MS says early next year. I hope soon!!!!
UA-cam should have a button for "Take a bow "...
Thanks for the cool and kind words, Raja!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Can you get the table header names too?
Hello I thought your video was interesting, maybe you can help me further, I have a column with an ID in the Excel sheet (EA3: AE22) in addition to the names (there are 20), in addition I have 3 columns, in cell AE2 I also have an Id the column next to it you will also find the name (that changed) that I want to look for in A3: CV21, (each name has 3 columns of data apart from the name), I want to retrieve that data and place it in the right place, Is that possible? For example, in AE2 the name Malacor comes that we find in B2, Now I want to retrieve the data from that column that is in column C and place it with the name (in our case, B2 can be found) and also find in EB2, the data comes on EC4, if you need more info ask, thank you?
I'm not finding SEQUENCE Formula in Excel 2016, Is it replaced with any other Function
What if I have multiple line items matching the cell value I need to look up --> cell value is invoice number, and several lines follow with the same invoice number, but different lines of the invoice. I need to pull all the lines for several invoice numbers from a huge file containing MANY invoice numbers. This formula only pulls one line matching the invoice number. How do I pull ANY lines w/ the matching invoice numbers? THANK YOU!!
Hey did you ever find the answer to this question?
365 is fun
How can we download or make active the advance functions
like unique, short, filter, sequence in Office 365? Please suggest.
You have to search Google for Office Insider, then sign up. Otherwise, Office 365 will get an update soon.
Thank you boss.
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
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: insider.office.com/en-us/join/pc
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)?"