VLOOKUP To Get Complete Record: ROWS, COLUMNS or SEQUENCE Function? EMT 1532
ฝัง
- เผยแพร่เมื่อ 11 ต.ค. 2024
- Download Excel File: people.highlin...
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: 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...
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 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 : )
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!
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
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 : )
Only one word about your Chanel! The best!
Glad it is good for you, Mergen!!!! Thank you for your support : )
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 TH-cam Channel: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
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.
You make it very understandable, thank you very much
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 : )
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 : )
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 : )
VLOOKUP. Always from a new perspective. Thanks.
Thanks Mike,
with SEQUENCE is awesome..!!!
You are welcome, Sevag!!! 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!!
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!!
@@wayneedmondson1065 , Happy New Year, Wayne!!!!
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!!!!!
Thank you for this amazing videos Mr. Mike Irvin !!
You are welcome!!!
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 : )
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 : )
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 : ) : )
Sequence looks amazing! Can't wait to apply this.
Yes, SEQUENCE is great! Thanks for your support, Ann!
office 365 seems really worth it!
REALLY worth it: Dynamic Arrays, Power Pivot, TEXTJOIN and so much more : )
Another Amazing video! Thanks Mike!
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 : )
How could anyone dislike this video. I love the stuff!
Thanks for your support, Remy!!!!!
Great tip, Thanks, Mike... I think I will go with sequence function.
Glad it was great! Thanks for the comment about SEQUENCE : )
Awesome!!! 😋😋😋😋. the New function sequence with columns and rows functions that is fun.
sequenceisfun : )
Dynamic arrays rock. And you rock man :)
Glad it all rocks for you, nimrodzik1!!! Thanks for your support : )
So neat solution. Thanks
You are welcome, Vida!!! Thanks for your consistent support : )
Hello Mike! Thank you for these great contents.
You are welcome!
SEQUENCE is awesome!!! Love it!
Glad you love it, Teammate!!!!
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.
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 : )
Yet another amazing video by Mike the awesome ... Thanks Mike
Your are welcome, Syed!!!!!
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: th-cam.com/video/XzNfSZBohXc/w-d-xo.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.
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!!!
Thanks!
Thank you so much for the donation, Byron!!!! : ) : )
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 : )
everything you do is amazing
Glad it is amazing for you!!! Thank you for support, Majd, with your comments, Thumbs Ups and Sub : )
Excellent dear sir, Thanks a lot.
You are welcome for the EXCELlence : )
Just Wonderful .... Thanks Mike
Glad it is wonderfulf or you!!!! Thanks for the support, Hussein : )
Simply Amazing
Glad it is amazing for you! Thanks for your support : )
Great video Mike, Sequence is awesome!
Thanks for the SEQUENTIALLY awesome support, Chris : ) : )
Thanks you so much you save too much time of mine. It's really amazing.
I am glad that the video helps, Asim!!!
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!
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
Excellent Mike thank you.
You are welcome, N Sanch01!!!
Amazing 👏 Mike
Excellent Boss
Excellent video!
Glad it is EXCELlent for you, Joshua : )
Amazing Sequence
Yes it is!!! Glad you like it : ) And thanks for the support, We360!!!
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!!!!
Great tricks!
Thanks, Teammate!!!!
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 : )
Really Bro, truly amazing............!!!!!!
Glad it helps, Adarshram!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
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 : )
Thanks Mike. At the moment, I can only practice on the old method (:
Soon, soon : )
Hi sir, thanks for wonderful tricks I need columns data from another sheet how I use this function.
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?
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 : )
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 😢😢
brilliant!!!!
thank you.
You are welcome, gyphon50!!! Thanks for the support with your comment, thumbs up and Sub : )
As usual awesome videos
Glad it is awesome for you! Thanks for the support, Awesh!!!
Amazing man, it works
Glad it helps!
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!
Kindly clarify, how to return entire records if Similar ID repeats twice in same coloum
As you said, totally amazing Mike :-)
Glad it was amazing!! Thanks for your consistent support : )
Hi Mike great video. I'm struggling to apply this to multiple table arrays. Any thoughts? Thanks!
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 : )
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"
GOAT STATUS
Glad this helps, Nyzaire!!!!
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!
Thank, it very useful
Youa er welcome for the useful video : ) Thanks for your support with your comment, Thumbs Up and Sub : )
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 !
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 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.
Very nice
Glad it is nice for you, jawed!!! Thanks for your support with your comment, Thumbs Up and Sub : )
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
Salute!
Thanks for the salute : ) Thanks for your support : )
Hello, is xlookup have this function as well? I mean can i combined to column function? Thank you 😊
@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.
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.
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?
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 TH-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...
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?
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!
365 is fun
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)?"
TH-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 : )
How come you don’t have to use ROWS() in the sequence function?
I did not expect that...
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?
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.
@@GeertDelmulle It is great to be on a fun and amazing Online Excel & Power BI Team with you, Geert!!!!
When will dynamic arrays be generally available?
MS says early next year. I hope soon!!!!
Can you get the table header names too?
I HAVE ONE DOUBT SIR ,WHICH DOUBT MEAN YOU SELECT THE TABLE AFTER USING THE FORMULAS IS WORKING.BUT SELECT AREA MEAN A1:D7 MEANS NOT APPLICABLE THE FORMULA SIR.WHY NOT WORKING THE FORMULA????
I'm not finding SEQUENCE Formula in Excel 2016, Is it replaced with any other Function
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?
Wow!,,,
Thanks for your support with your WOW comment, Thumbs Up and Sub : )