Learn Excel - Video 275- VBA - Vlookup with Loops - Advance Lookup

hi guys very good morning to you welcome to the video number 275 and the welcome to the YouTube channel as well YouTube channel where you can come and watch two hundred and seventy-five videos or nights alike so we've EMS access and access we made this video channel is dedicated to all those you know who are working extensively on the XL or the axis and they need to learn the VBA they need to learn the basic Excel at once exhale I have covered all the things for you right so in this video now I'm going to actually tell you that how you can actually use the loops with the vlookup function in the VBA right because I have received actually two comments one recently and the other one was quite I really don't remember but it it was a long time back so now let me show you exactly what we need to do but before I do that this is the comment actually which George you know how to attend hello what if the range is dynamic how that will work so I do revert it but now I would like to record the video as well and I would like to show it you know show show you also everybody practically how it is possible so basically there is a video called as you can see here might be I may not be able to zoom out it but this is a video number 237 how to use the vlookup in the VBA right but then guys you really don't have to search the videos you know by then the numbers you can simply go to the playlist on my channel and you just you will find out all these different different categories like so VP events XO VB introduction CDs access access macro so once you scroll it down you will find these all function videos in the playlist called Excel VBA functions so if I just scroll it down maybe if I would like to show you then possibly somewhere here you can see that okay maybe I'm not able to find it out that that's the video on the loops right if you go ahead and if you watch this you will understand how we make the loops and we have the basic intermediate advanced all the levels of the videos here so for the bigness for the advanced uses everything I have got in for you this is the video I was talking about when you click on this sorry the playlist you will have the toilet different videos we have talked about the different different functions like you can see that left right eye in this tiara is a reverse then we look up challenging using the array so theta these are all all that you know what was actually the videos and at the same time if you are looking for some basic videos you need to get the understand into school head and watch this video basic basics of we be a function right so these are the date as somebody asked me recently I'll - there's another comment actually I received from mr. macho and he actually wanted to see that how to convert the date in two months so mr. Tom if you are watching this video then as I explained as I reverted you also you can actually go ahead and you know you can see these videos like you see data can state that and they part all these interim you know this video called vv8 eight functions so you get everything right thanks by the way for writing and this is a video on the vlookup function which i have uploaded i think last month where I talked about the VBA how to use it in the VBA so this video is for those who wants to understand how to actually run the vlookup you know in a very dynamic mode now I have guys just prepared a dummy data and you can see here we have the ploidy name code and each and the state as an another sheet I've got here a plan table and I want to now find out the you know employee id's name code hn status using the BBF course so in this video absolutely I'm not going to talk about how to work with the vlookup even I'm not want to talk about that how to work with a you know vlookup when it comes to the programming when it comes to the VBA how to use it in the V way because I have already covered that as I shown you you know in those videos so you can go ahead and watch that this is only for those people who know these loops and who know thee to have a good idea of the loop and who have you know the good idea that we look up right so let's go ahead and let's start this how we going to do that as you asked so now you see the thing is here as you know in the vlookup we first of all would like to run a new which go in the downward direction and covers all these employee IDs right and then the second thing is across the columns we would like to also you know capture all this mean code and age and status so we are going to use the two loops one is that week I am gonna run this four times and each time it runs it will run further you know these many times these numbers how many numbers we have or one two three four five six six times so ideally four by six makes it 20 code and 24 times you your loop is going to one right and that's quite easy it's really not a big sign that it's very easy so let's go to the visual basic and let me insert the module here I will show you the links of this VBA function in the end of the video so that you can you know directly go there so no worries at all so loop with going to come this is really going to be very exciting and this this gives you a very good idea that how powerful loops can be so the first thing guys what I'm telling you see if I have to do it manually what I what will I do I simply use the vlookup I pick up this local value which is called employee ID and then I go in this sheet right which is my database and I select this database and I say that the column number is true because I want the name and the new name in this column is 2 right you know the vlookup it's very easy now what happens when you're going to drag this formula here how many times you basically you've got to drag it obviously based on your headers how many headers you have you have code headers right so that means here the loop should run four times right so what I'm going to do I'm going to declare here let's say them and I'm gonna declare here a variable called four columns as long okay and then what I'm going to do then pick a pattern for we're going to run the loop so I will simply write 42 - one - how many times you want to run you want to run four times right so this is first thing okay next this is your loop now this means that the loop is going to run four times now each time it runs basically what I have my plan is to first drag this formula down and then go to the you know the code and then again drive it down and then go to the age and then again drag it down so what we need to do is we need to further write one more loop which is going to run one two three four five six six times okay so what I will do right here for I equals to 1 to 6 in the downward direction we will draw it right and then let's declare I is long on the top because we need it ok so now this inner loop is going to here we are going to write that the vlookup okay so let's go ahead what is your target what is your first cell which you're going to want to target so here I want to target Evo right now Eco comes on the as you know fourth row and it's on the fifth column right so I'm gonna right here cells and five and so because here this is your going to be the look of value now obviously I don't need the five because next time I want 300 400 eight four five so the row should increment you will rather than that I will create a variable so let's create a variable our is long okay all right and for the column also let's create a column C and let's pick lay the column here so we can write here C as long okay now I should start with as you know as for my table it should start with four and in the same mind just put this cooler not a semicolon use the shift key you can write all the variables you can allocate the values ingesting the line just toward the space otherwise you can press ENTER and you can write it right that's all up to you okay so the column has to be tried all right so this means that I'm gonna use the vlookup on the photo and the fifth column very nice which is going to be obviously you know ee4 right okay now worksheet function i have explained this in that we look up with you we look up this is how you write what is the first parameter guys when the first parameter is you got to select this value which is hundred now how i gonna select that obviously hundred is also on the same row right where we are trying to find it although this name so the row will remain same but the column will change so what we can do we can write here cells because we will need to look up this value which value the value which is in the row number which is r which will be four of the say biggest name is also on the same row and the column would be four so i don't have to create another variable for this because you know in the local value every time when you drive asam line the dogwood direction it will always be the column number 4 because in the column number 4 which is your column number D you have your employee ADIZ right so this is going to be your value look up let's press the comma argument to now argument 2 if you remember it's your table right so how are you going to write the table so table I will write it by going to that sheet watches sheets sheet 3 that's my sheet name if you remember when I was using the vlookup manually you know I went to that shape and from there it has to be range I don't remember the range but let's just assume that it has to be 50 we will change this argument 3 now what is the argument we know guys argument thing means which column number do you want now name is on the second column if you remember let me show you here first of all the column is b2 - f8 so let me correct that b2 to f8 all right there we go no you know that it is it the column number has to be 2 because we need the employee name right and then next time it has to be 3 and then 4 and so on right so the column number what I will do because I have to change this column number again and again I'll write here simply C well anyway M and for the exact match I write 0 and that's it and we have to declare the column number as well so let me declare everything in one line as long and so again colon and column number should start with war so the column number should start with equals to 4 aside - right ok so now when you're going to run this loop let's see how the things are going to happen ok so let's just go ahead and put a little up so that we can have a more space I don't need this let me yeah that's fine and let me decrease some space from here is with so that I can shrink the sheet and I you know you can also see how it the things are going to happen ok there we go very cool and all right now let's go ahead and run this so I'm gonna run this while I'm on the sheet phone okay so there we go so let me go on this there we go everything's done now this is the first time the bigger loop is running so first time we are going to run a me and this first time we're going to you know what six times we are going to run the loop further you know six times so now what is the value in the CRN see now you can see that our is four and the C is 5 that means I'm exactly on e4 okay so in the e4 what we need to evaluate we need to look up this value which is again 4 by 4 4 comma 4 which means I'm on the fourth row and the fourth column which is hundred 100 is going to look up in this table and we will have the column number two which has to be the name so let me just press f8 and there we go you can see the background right the next now what is the next the next is that you know is going to be 2 and so I is going to be 2 then next time this are actually should increment right but I think we haven't incremented so what will happen when I run this you see that nothing will happen because every time this same thing keeps coming in because you're not changing the value so if you look at if you if you just select this entire code and if you put the watch of this see what happens basically you're looking up you know not exactly this value let me delete that oh we are actually trying to find it out this value okay so let me the watch chord on this let me drag it and put it here yeah you see that every time I run this loop what's wrong with this code biggest every time this hundred is coming so hundred is coming so obviously every time the heard nerd would come and you know you will have the same value so what we need to do whenever the vlookup happens we should go to the next row as I was telling you right so I'm gonna write R equal to R plus 1 okay you don't need any changes anywhere I believe that's it R equal to R 1 and let me reset the code and let's run it again there we go okay and by the way let me also delete this okay now let's run this okay you've got the a you are in the next room now please have your eyes on this value point because right now it is changed to 300 right 300 why because now the next is 300 so 200 is going to look up and you see that I've got the B right because it o is incrementing right so this cell is also implementing now the next time when you come here it's on the sixth row and the column is five sixth row and the fifth column what does that mean obviously that means you are going to talk about the e 6l okay so in this way you see the loop is going to run perfect now it is finished it goes in that bigger loop which is this next which is this whole column let me just put some space here right no the next time in the column runs and when the loop runs you see this for under scope called is going to be equal to 2 that means I'm now going to run this loop the second time the bigger loop right for the cord actually right that's why I have written but the point is you when you are out of this inner loop you have to start some again from the rule number called 4 so right now your R is actually 10 so that means it's going to start from the tail so if you run this loop you know everything will be messed up you see that it says unable to get the vlookup property simply because of the reason that you know these values we need to convert it back so once your inner loop is finished then in this next which is the bigger next this is a bigger next what you need to do is you need to come back to the rule number called four right because I should be on the code now biggest name column is 12 so to go to go to this I need R equals to 4 and at the same time very important line the column number this we look up in dates it has to be again incremented and it has to be plus 1 because next time you need the code right now when you need the code obviously guys this is what exactly you know I'm talking about the third column number right and what else we need I guess so this C is also you're supposed to change this because when you start then the C is going to be fine right because you started it from the C equals to five you can see this but the next time the code is on the call of number a so the column has to be changed so C has to be change basically so C should be equal to what C plus one right now if I run this again let me show you let's just delete this everything and now let's run this okay there we go so we're starting it as you can see under is the value which we are going to look up in the table b2 to f8 and the column number has to be two which is a column for the name so this is going to quickly run let me run this done now we would like to come back to the rule number for the column number which right now is two it is going to be incremented by three so that next time this column number should be three because we need the code okay then C goes to C plus one now C is right now you see five five means that I'm on the AE column but now I want to be on the eighth column to use my vlookup so that's why I'm incrementing it so now the next time when you run okay no next time when you run you are again back on the hundred so hundred is going to be lookup this is this is the hundred this cell R comma four dot value equals 200 hundred it's looking up in the table b2 to f8 which is in the sheet three and it is going to fetch the information from the column number three so I should see here a code number Wow amazing now again we will increment the row number so if I run this quickly you see that I got here everything similarly again I'm you know coming back to the rule number four because I have to be on the age and to start with the age I got to be on the fourth row and at the same time the column should be six so that's why I'm incrementing this equal to C plus one and this of course this column number indexes well because for the age we need to be on the fourth column right so in this way guys if I run this quickly you see that it's gonna walk so this is the time it is working because you know the value of the for under scope col' 8.24 and we have said that it has to run four times so ends up all said way simple right no you know if you if you if you have seen my very basic videos on the loops then this can be very easily you know we can make it dynamic what you can do is instead of four because you may have some new headers every time you know so what you can do is you can simply write here range and then you can start from the you know yeah your this the row number basically what is yourself so I won't do what I want to do is I want to go to the last cell except T X repeat except except TT and I would like to use the shortcut key called control left arrow this will take me to the last column so I'm gonna write I'm going to write except T three and then exhale left and I need the column number for that right that's it this will make it dynamic and similarly here because I need the rune abuzz I start from the let's say one lakh and I'll move up just a second let me yeah Excel up and our true I specifically talked about you know these functionalities in normal video so this makes my entire task dynamic now see what happens if I simply just remove this let's say I remove this okay and now when I one more thing I add here let's say I add here qualification okay so if I add the same thing here in the main database this one and let's say I add here graduate postgraduate any and let me simply right here this is what exactly you know we have made the table now what do you need to do now the only which you need to do is in your vlookup you have to increase your table which is called D right now it is it is going to be the g8 right so you can write here g8 because your column has expanded all you can also do one thing you can further go ahead and make this as real dynamic that's all up to you guys right because you can again capture the last column number the column on which column you are and you know from there you can definitely go ahead and calculate the the basically the column you know on which column you are so that's very pretty simple but I would just like to keep it it you know simple as g8 so let let's now run the macro quickly you know everything is clear so let's go ahead and run the macro so I'm going to run this macro and let's see we are going to get the values or not so there we go let me run this quickly oh wait so there we have some problem and the problem is it actually runs in a different direction it first covered these points and this is something I never wanted to do that because we wanted to go in the downward direction so let's see what is the problem guys right this is very awesome now let's see what is why we are getting this problem we will troubleshoot it so first of all let's run this okay now what is this this saves nine oh yes I got some point now you see if this range X 83 dot and exhale to lift is giving me nine right because of my stupidity because obviously when you're going to use the control you know the left arrow by two to go to the last value which is this value you will pay on the high column so i column means that you know it has to be obviously which column number it has to be ninth column number but you do if all four your V lookup table it's not the ninth column it's basically the column number called sixth right so how we gonna adjust that it's very simple you know that you have three headers before this so to you know level it with the Excel columns we need to subtract minus safe it so I'm just gonna subtract minus C from it and I hope now this time it should work similarly guys this is going to now this is going to return Miu what if I put the just watch code on this I add the watch to see that what value it is going to hold let me run the f8 no this is against showing me the 1 right which is wrong why because I'm starting from the a I should have started from the column D so that's my mistake right because in the a is empty so basically what I'm saying that go to this one lakh room and you see control up arrow to get the last value so if I run this again you see that it says mine but for me lookup table you don't have to run this 9 times 9 is coming because you are obviously when you're going to be all let's say on this cell then like say let's say and use the control up arrow it is going to take you to the 9th row because that's the last value right so we need to see that how many rows we have we have 2 rows above this with a bland right so blank or whatever but we need to subtract so I'm gonna subtract -2 from it right so this I think is going to balance everything let let's just run the code let's have a look so now we are going to run 9 minus C we'll make it 6 so that means the loop should run six times so is it correct 6 times we need it 1 2 3 4 5 we need it only 5 times because this is also you know including the employee ID but I don't need the employee ID I need I want to start from the name so obviously I will further go ahead and subtract both from it which means a column B column C column and B column right and similarly when I run the code again and here I would see 9 9 minus 2 will make it 7 how many times you want to run this loop 100 300 400 so 1 2 3 4 5 6 so you don't need to count the header so that's why I'm going to keep it 3 let's go ahead and let's run this alright there we go it's working very nice and let me quickly run this as you can see alright so there we go guys finish and you have your qualifications right so now you can add anything I think that's perfect so if I quickly add here marks let's say I want the marks here and I go here and write marks as well let's say anything let me just write some any number quickly right so what I'm gonna run the vlookup now I just want to make ensure that my table should get incremented and I'm going to just you know gonna write here this H so let's make it edge and that's it so let's just run the this macro quickly and let me read everything and also let's quickly create the button here insert into the button you go to the insert and I'm going to just right click on this assignment and there we go let me run this Oh wonderful you see that it's actually working like a magic the very simple now I know you must be thinking one thing which actually is pausing me you know what can we not make this time table are still down on me what is the need to you know every every time you write come here and you know you asked me to do this which is keep on writing a cheat and I eat you I'm and as the table expands or strings you know so now we can do one thing I guess we let's declare the variable here isn't it we can declare a variable and we can you know fix that variable here maybe for example let me declare here one more header which is called let's say adult okay so I write here yes yes yes no no and yes okay now instead of actually changing the V lookup table in the VB air oh I know that the last columnist I it's I actually so I just right here I okay you can ask you you know the person who is going to run the macro you know that come and write I hear or may the sheet fool that's all up to you how we want to ask you do the things right so maybe I can write here I just makes more sense or maybe biggest you know that's your table to better you right here I okay and you can give it a color called green so that they should know that there is something here no the value is in the a1 which is I so what we can do is to make it clean I can declare one more variable and that is to capture or I should be right here that last column as frame because it is it is going to be string right now the last goal okay address I would write address that's this much better I'm going to write here sheet in the sheet don't forget to write the sheet name otherwise it can capture any other sheet as well so I'm gonna write as sheet 3 dot range e1 dot value this gives me I okay so we have to change the last goal here as well so I'm gonna right here edges perfect okay now when this is going to be run this will have I and then what I will do I will copy this and here look at this now this is very important I know that the last row is 8 right further you can find out the last row I leave it to you now you can contact it you you know how to find the last row right I have uploaded the videos also and I have just actually used this - with the range t1 like you know dot index Allah so you can use the last row from there but that's what the focus is important what is the column number so I'm going to concatenate it with your variable called last status and further eight let's create the space here like this and I guess that's what you need and it is the number so we can leave it like this yeah I think that's fine now let's go ahead and run it let's see you guys I got the adult here and I'm gonna add the adult header here here is well I don't and let's just run this and I'm going to delete this let's see now if if we are able to capture this or not okay so I'm going to delete this let me run this variable not defined all right so which variable there's some spelling mistakes I believe yeah it's a su double D let me reset the code let's run this again oh yeah there we go guys great awesome pretty awesome right now further if I just delete it you know to read the headers let me just let it it delete them all right now let me run this okay so it's working right all right let me delete this again I just now let me run this you see that it's actually working perfect right so similarly you can further go ahead and if I if I just copy all the headers again and let me just tell you one more thing I know you must be expecting that so let me cover this in this video only but before I do that lets say let me write one more thing here let's say I'm gonna write here the mass let's it max - okay someone right here this this this this and this okay and just select everything make it the same font okay now we have the mass to here and I'm gonna write here max - max - okay and let me run this unable to get the vlookup property oh you have you see that why are we getting error like it says my mistake this last Col address is I and I cannot go here because in the I value the when the vlookup will work it will create a problem here because this if you see this will create a problem because if you want to find the marks it's in the L column right so as I said you need to change the column number here so I'm going to make it any okay now let us run the code so okay let me delete this and run the code oh there we go pretty awesome guys right you can further boo it and you know put everything in the st. alignment now many of you again would be thinking that what if you have a different headers for example right now my headers are exactly in the same way you know like I have a name code each status marks it dealt marks to everything in the same order and that's why the loop is working but what if I let's say delete everything and I make this legit code and I make this age and I'm right here status and I write here you know this word got qualification and then here I write something else let's say marks and then marks - right so we can I mean if I run this same thing again you won't get the same answer this is what exactly you will get why because you have given the numbering based on believing that your headers and the column headers are going in the same direction if you are finding out the name then it is also in the second column and in your table also it is on the second column which in this case is not the case and this is how the practical situation comes right so we can do one thing instead of because we need to automate the thing so what you will do you use the match function here I've talked about this function a lot a lot a lot you pick up the playlist on Excel vlookup and match and see that why we use the match in the vlookup it will make you like very easy I have talked separately on the match function as well from this in the same playlist right see that the how to use the match function so match actually turns the column number so I'm gonna use this lookup value lookup this value in this table that's the table and the exact match we are going to simply close it now you got the tree so what is the benefit now anytime you change the headers you will have these column numbers coming accordingly and that's what I need actually right so if I copy and drag it now you see if I if I need the age here this number is automatically going to change if I read me needs a name here this is going to change so everything is you're not going to work in this way now in order to automate these so we will refer them you know with the cells called within the code you know with these the green cells so what you can do is in that case the column number which you have defined guys here too that is not going to work okay so we will have to simply remove this line because who knows from which column number we want to start so I can remove this because in this case now as I said if you need the inch you need the column number for because H is on the column number four right so in this way we will have to be dependent upon these column numbers so what we can do is in that case we can simply do one thing I'm going to give you a trick now you see that you have got the four two five six seven and everything now let create let let's go here in the in this cell in this code and here instead of this column number now what we need is basically we need a cell which is on the first column and the eleventh row okay so I will write here cells and the fifth row and the column number so for the column I again I'm depending actually on the column number because column number is going to change one by one right but in this way I can write it it will always kill the fifth row that's for sure and this column number again is going to be incremented so we are already getting it incremented right so that means next time that when you start here sorry I should have actually deleted I'd call them number so let me just declare it as long and here in the column number so for the Kolar so from where you want to start the column number has to be I believe five yeah e column is a five now please don't be confused I'm not talking about this whole column number I'm talking about this e right if this e and this e the row number 11 and the call of number five so row number 11 it has to be labeled on the row number 11 and the column number five which would come from here what is the value it's the value called four so that is going to be used there next time when you are out of this loop when you're going to finish up this and you go to the next column what you want on the same row 11 you need the another column which is six so combination of eleven two and the sixth well make you you know fall on this cell which is called to f11 right so that's why this column number is going to be used here okay so I guess we are fine with this we can run this code quickly let me just run this and we can see we got the age we got the name what about the status we got the qualification absolutely everything so good guys why don't we go ahead and change the headers right now let me make the name here let me plz making sure that when you write these headers the Spelling's has to be same otherwise you will be in a problem because match will give you the error if I write here status like this tt obviously this will give you the error because match will not be able to find this status you know and this header so that's again you need to take care of that so I'll write the status okay and I write here adult and I right here marks one now you see that I'm getting the error obviously because I think there is no actually marks one it's just marks yeah there we go I don't need this let it be like this now time to run the macro again let me run this Oh guys this is wonderful you see that you have done all the things right so this is how you you know make the vlookup dynamic with the loots a lot of work we have done and a lot of troubleshooting we have done but this is how you learn the vb programming you can watch this video again and again and if you have any questions let me know about it and if you haven't subscribe to the channel guys as I always say as I always request please do subscribe and you know enjoy the channel enjoy all the videos I will be back thank you so much for watching thank you so much for your time bye bye

Channel: Ajay Kumar

Views: 21,457

Rating: 4.8700361 out of 5

Keywords: vlookup vba, use function in excel vba, excel basic training, vba course, learn vba online, loops, how to use vlookup dynamic, text functions, mid, left, right, vba vlookup loop, vba vlookup, loop vlookup, vlookup using vba loop, vlookup in vba, vlookup loop vba, vlookup vba loop, excel vba vlookup, vba, vlookup, vlookup in vba loop, loop vlookup vba, excel vba vlookup loop, vlookup in loop from ajay kumar, vlookup loop, advanced vba, vlookup vba excel, vba vlookup multiple

Id: wgpko6q5U6U

Length: 38min 17sec (2297 seconds)

Published: Fri Dec 02 2016