Another powerful function of Microsoft Excel is vlookup. Using vlookup in excel, you can do so many things. for that you have to know the basis concept of vlookup function very carefully. If you know the basic things of vlookup you can use it in many many ways and many many places. So here we are presenting the basic concept of vlookup and one of the simple example of vlookup.
So Lets learn the basic things of vlookup function.
lookup value : Look up value means the cell in which we have typed the value. Here the lookup value is A2 because the we want to take the information of serial no of main sheet to 2nd sheet.
In B2, we want to bring the value of Oliver. Oliver has serial no 1. so we are writing same serial number in A2. Similarly we want to bring Jack in G2.Jack is in SN 2 in main sheet. so we are writing 2 in F2. Similarly in L2 we want to bring Harry. So we are writing 3 in k2.
That means the value in a2, g2 and k2 i.e. 1,2,3 are the lookup value.
Table Array : This means the range of cells from where we are bringing the data. We have that data in Sheet Main with cell range A2:D25. So for our use table array is Main!A2:D25. To make it constant we can use dolor sing. As $A$2:$D$25.
Col_index_num: This tells us the number of column in range from where we are bring data. Here in our range we have four columns: SN, Name, Age and Blood Group.
In Column B2 of 2nd sheet we want to bring Name. Name is in 2nd column. So the col_index_num is 2. Instead of name if we want to bring Age then the col_index_num is 3 and if we want to bring the blood group col_index_num will be 4.
Range Look UP : This tells us that what ever we are bringing should match all the criteria or not. Just if we want to see the same like value or exact value. If we want exact value we will write "True" otherwise "False".
Generally we use "True" in this value.
If we have sorted data that means the main data is in ascending or descending order and if we have made the range constant then no need to write any thing in this value.
Here you can see a simple example of vlookup where we are converting data in one column to three column. In this case we can use excel vlookup function and make that portrait data in columnar form.
For that we can do the process as follow.
Suppose we have the data in the following format in Main Sheet. Here we have data in row wise format or portrait format.
Now we have to present that data in three columns as given.
By using vlookup we can do it easily. Follow the process.
First make three columns as follow.
Now type the serial numbers as follow.
Here you can use drag to write numbers quickly.
Then it looks like this
Now in column B2 use the formula
In celll c2 change the last value 2 to 3. (col_index_num) as :
In Cell d2 change the formula like this : (3 instead of 2)
Now you can copy same formula in g2, h2 and i2 but change $A2 to F$2 as shown
In cell L2,M2 and N2 change $A2 to $K2
Now just drag the formula and get the result in column 1.
Repeat same process in 2nd column and 3rd column.
You will get the final result like this.
this is one of the simple example of vlookup.
If still you have confusion you can write on comments. We are ready to make it more simple.
Similarly if you have some data and want to analyze you can write to us. we will do it as your requirements.
If you have any query in any steps or if you are lost in any steps do write on comment. If you need the excel file too we can provide. Just write on comments or mail us and we can forward even the excel copy to you so that you can do it easily.
If you need to convert in 2 column or 4 column or any number of column you can do it. or you can write to us for any assistance any time.
In next tips I will show you how to inverse of this process. That means how to make the columnar view to portrait view using vlookup.
Please visit this link too. Thnak you.