Microsoft Excel is one of the most popular spreadsheet software ever. Almost every person use this tool to calculate, organize, store, analyze and collect data. We perform different calculations with MS Excel. MS Excel is a famous spreadsheet software. Do you know about spreadsheet and what can we do with spreadsheet? If no, then I will try to explain you in simple words. The spreadsheet is a computer program used to calculate, analyze and store our data in tabular form and we can do anything and perform calculations with it. Since, excel is a spreadsheet software so it contains many functions.I will show you vlooukp function for multiple sheets with example.
When we talk about the spreadsheet program, the first thing comes into mind is MS Excel since it is the most commonly used spreadsheet software. Excel provides us many features so that we can easily do our calculations. There are number of functions like addition, subtractions, average, Vlookup etc. Functions we can say that they are the predefined formulas which ease our task. Vlookup is also an excel built-in( Predefined) function. I will teach you to use vlookup for multiple sheets with example.
Vlookup function is considered to be very difficult to use and even there are premium courses available on the internet of just Vlookup function. You can consider that how much advance functions Vlookup is. But I will try to explain you in easy way so that you can better understand this function. I will show you step by step procedure to use Vlookup for multiple sheets with example.
Vlookup for Multiple Sheets with Example
Now before we start to use vlookup for multiple sheets with example, I want to clear your mind with important things about Vlookup function. You must remember these things in order to use this function correctly. Take a look on these important things:
1) What is Vlookup Function
is an MS Excel built-in function which is used to lookup and retrieve data
from the spreadsheet. If we have a large spreadsheet of more than five hundred rows and columns and you want to lookup a special value then Vlookup function will help you in this regard rather you find that particular value manually by looking the entire spreadsheet. Vlookup function saves our lot of time and effort and we can easily find desired value
2) Vlookup function Parameters
Vlookup function comes with four parameters only. These four parameters are given below:
- lookup_value: It is the value we are looking for
- table_array: I which table or array we want to lookup or find value
- column_index: It is a column number from which you want to retrieve your result
- range_lookup: It is a match code i.e, exact match or appropriate match
3) Not a case Sensitive
It is important to note that Vlookup function is not a case sensitive. For instance, word “SALARY” and “salary” are same to Vlookup function. So be very careful about it. We can say that it is the limitation of Vlookup function. It considers Uppercase and Lowercase as the same one. There are solution of this problem and you google in this regard.
4) Matching Modes
In Vlookup parameter, there is a range lookup and it is actually a matching code. It means, do you want exact match or appropriate match. Usually users prefer to exact match on appropriate. But it depends on situation. We use 1 for appropriate match and 0 for exact match. These were two matching modes of Vlookup function
5) Vlookup looks only right
I will say that this is the biggest limitation of this function. Vlookup function finds or lookup a value to the right side only. It means that this function only gets value from the right where we applied this function in the table. I will show you with example. See the below snap shot:
In above screenshot if we use vlookup function with Name then it can only lookup in Age and Salary column. I hope you understand, what does it mean that Vlookup looks only right.
6) Vlookup vs Hlookup
The word ‘V’ in Vlookup stands for Vertical. It means that the data must be arranged in vertical arrangement in order to apply Vlookup function. Same is the case with Hlookup function. The word ‘H’ in Hlookup stands for Horizontal. Mean data must be arranged in horizontal form in order to use Hlookup function. Vlookup looks in vertical and Hlookup looks in horizontal. But in this article we will only use Vlookup function and later I will go ahead for Hlookup function.
Use of Vlookup Function for Multiple Sheets with Example
Now, it’s time to teach you that how can we use vlookup function for multiple spreadsheets. I will show you with examples and screenshots so that you can understand fully. First, I want to show you that I have to spread sheets named Sheet1 and Sheet2.
In Sheet1, I have listed the personal details of employers. See this sheet below:
In Sheet2, I have listed the Job details of those employers. See this sheet as well:
Now, I want to show our goal. What will we do with these two sheets. Our target is to retrieve the salary of employers from Sheet2 to Sheet1. Salaries are listed in Sheet2 and we will get this data in Sheet1 with the help of Vlookup function. So read the below section to understand fully, what will we do to achieve our goal.
How to do Vlookup
Now, remember the parameters of Vlookup function. Now, see the below steps and do the same in your case so that you better understand.
1) First, select the cell where you want to write or get your desired value. In my case I have selected F6 cell.
2) Now, put equal sign (=) and write “vlookup(” function as I have done below:
3) Now, select the lookup_value( The value you are looking for). In my case, I am looking salary for the “1601” or C6 (Cell address). So, I will select this cell. You have to select the cell according to your situation and press comma for next parameter.
4) Second, select the table_array( The table or array in which you want to look a value). I want to lookup a value from Sheet2 in my case. You have to select the table or array according to your calculation. So, I will select the entire Sheet2 and put comma.
5) Third, write the column_index( Column number in which you want to find a value). In my case, I want to find a value in column 3 (Since the salary column is in Number 4). So, I will write “4” in my case and put comma. You have to write according to your case.
6) Last, write the match code (exact match or appropriate match). I want exact match so I will write “0”. It is optional and you can skip it as well.
7) Now, our parameters have been completed now, press enter the result will be appear in the cell where you applied the vlookup function.
8) Now, I will move the cell handle downwards to apply the same formula on remaining employs to retrieve the salary of remaining employs. I don’t need to write the formula or function manually.
I have tried to explain you that how to use vlookup for multiple sheets with example. I have taught you step by step to use this complicated function. It is very easy. Now, teach others so that you can also understand this function. Keep practicing on multiples spreadsheets. Soon, I will try to teach you about the use of Hlookup function.