How to use VLOOKUP in Microsoft Excel
Today, I learned about one Excel Sheet Formula "VLOOKUP". It is very useful formula that gives us the power to search a value in a particular column.
Problem that VLOOKUP can solve.
Let's say you have one Excel Sheet of Employee Data. You have 2 sheets inside the Excel Book. One sheet is for Employee ID, Address and Name. And in sheet number 2 you have Employee ID and Employee Address. In Sheet number 1 you have only Employee ID and Name data present, you want the address of all the employees. Address can be found from the sheet#2. If we go manually and do search for address for each employee ID and paste the same data in Sheet1 then it would be a long task and boring stuff to do :(
In order to automate the search functionality VLOOKUP is there in Excel Sheet.
Let's solve our previous problem, see below is the Sheet1 we have named it as main. It has 3 Cols EmpId, Name and Address.
Below is the image of Sheet2, lets rename it to address. It has 2 cols; EmpId and Address.
Now in order to search Address we have to take the EmpId one by one from sheet1 and check in Address sheet that if the Employee ID is found or not. If employee Id is found that we need its address.
VLOOKUP formula building
In order to build the expression for VLOOKUP, please Follow below steps.
Step1:
Click on fx in sheet1, it will open up an Insert Function Pop up Window. Then Type VLOOKUP in text box of the pop-up window and click on Go button. It will search and select the VLOOKUP function then click on ok Button.
Step2:
This step is crucial, now we are going to build the expression that will do the search. VLOOKUP has 4 arguments:
1) Lookup_value: It is the value that you want to search for. We have written A2 because we want to search for first Employee Id which value is 1 and it is in A2 column. Lookup_value: A2
2) Table_array: As its name suggests, it is an array of semicolon (;) separated key value pair data. Key value paris are separated by comma (,). For example: {1,"Brajrajnagar"; 2,"Jharsuguda"; 3,"Mumbai"}. It is the data through which the function will look up for the value we are searching for. For Table_array field, you have to write sheet name (from where you want to search address) and the column range (where the data is there) separated by Exclamatory (!) mark. Like in our case, we have to search in address sheet and our column range is from A2 to B4 (A2:B4). Therefore, we will write address!A2:B4. Now, this is perfect however, if you see in my above screen shot, I have written address!$A$2:$B$4. Each column name is separated by Dollar ($) sign from the both side. It is because, if you want to write the formula for the next value; say for EmpId = 2 then there you don't have to write it manually. You can select this formula and by pressing Control Key, you can drag this formula to the next below cell and it will automatically create formula for the next column. If you don't write $ sign with column name then above drag and drop formula will not work. So finally: Table_array: address!$A$2:$B$4.
3) Col_index_num: This is the index number of the column from which it will search the value. In our case it is the address column of sheet2 and its index number is 2: Col_index_num:2
4) Range_lookup: This is a boolean value either TRUE or FALSE. If we select TRUE then it will do like search. If any one of the value matches or looks like the value searched for then it will fetch that data. However, if you select FALSE then it will do exact match before selecting the data. Range_looup: FALSE
Step3
In this step, we will create the same formula for rest of the rows for 3rd row and 4th row. To do that just select the First cell where we wrote our formula that is in C2 and drag it to the C3 cell by pressing control key. It will automatically generate the formula for C3. Similarly you can select the C2 and drag it to up to C4 and it will generate the formula both for C3 and C4. Also you will see the correct address displayed on the selected cells.
Conclusion
This is the complete explanation how one should write the VLOOKUP formula. I hope you enjoyed it, give me your feedbacks and let me know if you know something better or you wanna suggest something. I am the fledgling guy in Excel world :) Please download the Excel sheet that I discussed now from this link Example Excel Sheet VLOOKUP.
