Vlookup In Excel from different sheets

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Vlookup In Excel from different sheets






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!








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok