Vlookup in Excel from different sheets

Hello Everybody and welcome to Excel Made Easy!
This is your Excel guru. Today with a formula called Vlookup. Now this is another version of Vlookup, Vlookup from different sheets. I have taken an example over here. An example is for a class of students whose names are John, Mike, Arman, Rajiv, Ashish, Charlie, Karat, Raju, Janice and so on. Oh we have the latest hottest Indian sensations Munni and Sheela too. And say these are the subjects say English, Maths and Science. We want to get their marks how much marks did it score in these subjects and in sheet 3 we have the data, we have the names of the students and the marks of these agents, of the students in these different subjects. Now you see that, that was just a trick.

The marks in English, maths, science, history, geography and French. In sheet1 we only need the marks for English Maths and Science how are we going to do that so we are going to use Vlookup so the Vlookup formula starts with is equal to VLookup bracket open now first thing what is says lookup value that means what do we want to look up for, based upon this data Vlookup would search the different, the other data, so we have to specify based on this, search the other data. Now you see this is highlighted and it says to go to the next thing table array you need to press comma so now comes table array, table array is your data range where is your data so you need to click on sheet 3 and select your complete data this is my complete data so I selected and then I press comma it says comma and now, now comes the important part it says column index number now the column index number means that every column this is called a column, a vertical stack is called column and horizontal stack is called row so Vlookup says from which column do you want the data for John. say now we are looking for marks in English so which is in column two why is column two because the data the table array which we select the first column would automatically become column 1 so second column is column 2. So I need to say 2, so now it says approximate match or exact match. So if you press 1 it will say approximate match if you press 0 it will give you exact match.

So I’ll press 0 bracket close and enter, and here is your result 98 Lets just check. John got how many marks in English. John got 98 marks in English. Isn’t it cool? easy to do there is another way of doing it very simple one again. So we want to see how many marks did he get in Maths so what we are going to do is we are going to click on formulas insert function type in the name of the formula, Vlookup click on go so it says Vlookup, which is lookup, Hlookup, If, Match, Countif . You know what as of now I have covered Hlookup, If, Countif, look up and match I am going to cover in the next few days. Let’s see. I am planning to take this video library to around 1500 videos. Ok so look up value is again is this area this range, the names of the students, table array is my data range, where is my data range, this is my data range, I have, you see its showing us data because I named it data. And column index since I’m doing it for Maths so the column index for maths was two. And Lookup range I want say 0 because I want to get exact match. is equal to 98 I actually did a mistake it should be three because maths was three. Let us see Maths was three this is I mentioned the column numbers for you. So nice cool.

So now I’m going to teach you one more thing how to copy Vlookup. Now this is very specific very specific. You need to be knowing what you need to do. See I have applied Vlookup over here, Vlookup over here. Now you see in both these things both these formulas if you see C11 to C24 data, 2, 0. C11 to C24 data, 3, 0 what has changed the column number Now if I copy it then if I just put the column number from here it will give me the result before doing that we need to do one thing we need to edit this formula press F2 we go into the edit mode of the formula, select your look up value range, value and press F4 press enter, ok. Now do a control C do control V It has given you the same result. Now what I am going to do is I’m going to press F2 again, I’m going to edit mode. Science was in Column 4, I just type in column 4 and enter. Answer aa gaya and we have the answer. I’m Sorry, I switched back to my native language Hindi. I’m so sorry. so we have, this is how we can copy Vlookup and we can do the same for. Now this copying was for different in columns. See we went from English to Maths, from Maths to science. If you want to copy Vlookup just like this to other students also all we need to do is just copy and paste. You’ll get the result. The copy feature I taught you was to move horizontally from here to here but to move it vertically all you need to do is just do cut copy and paste. I hope you liked it. Well the excel file shown this training videos is available for free download at my website Excelmadeeasy.weebly.com. All you need to do is go over here and click on the training files. When you go to the training files you can download this training file for free without any charges no I’m not looking for money if you want to give me anything yes go ahead and give me a like on Facebook and give me a subscription on YouTube because when you do that it helps me in spreading the word that yes let’s share knowledge.
So this was your Excel guru with a formula of Excel called Vlookup, Vlookup from different sheets or from different workbooks if you want that
So this is your Excel Guru signing off for the day
Have a wonderful day ahead!
Happy Excel learning!