Hi, Previously I wrote about Pivot table, this time lets explore VLookup as this is also one of the very powerful and handy feature of Excel.
First lets understand why we make VLookup. A lot of people use Excel as a database, may be due to reasons like business requirement or unknown to the power RDBMS. Although excel can work as a database, some of the tasks that would be very easy in a database program are fairly complex in Excel. One of these tasks is matching two lists based on a common field; this can be easily accomplished using Excel VLOOKUP. In RDBMS, this concept is more like Primary key that is used to establish relation between two tables. You will find the function VLOOKUP to be extremely useful so check out an example of when and how to use this function below.
In this example, the lookup table is on a sheet named EmpMaster.
Create a Lookup Table:
Lookup formulas can work vertically, looking for values down a column, or they can work horizontally, looking for values across a row. In this example, the information will be stored vertically, with values down a column.
1. Enter the headings in the first row as show in the picture aside.
2. The first column should contain the unique key values on which you will base the lookup. In this example, you can find the salary for a specific Ecode.
VLOOKUP Function Arguments:
The VLOOKUP function has four arguments:
1. lookup_value: What value do you want to look up? In this example, the Ecode is in cell A7, and you want to find its Emp Name.
2. table_array: Where is the lookup table? If you use an absolute reference ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
3. col_index_num: Which column has the value you want returned? In this example, the Emp names are in the second column of the lookup table.
4. [range_lookup]: Do you want an exact match? Is an approximate match okay?
If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the Ecode is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE.)
Create a VLookup formula:
Once you have created the lookup table, you can create other formulas in the workbook, and pull information from the Employee list. For example, to prepare Deptartment, you can enter a Ecode, and formulas will get the Emp Name or salary from the Employee table.
In this example, the Depot is created on a sheet named Dept. The VLOOKUP formula should find an exact match for the Ecode, and return the Emp name.
1. Select the Dept sheet
2. Enter Ecode A15 in cell A7
3. In cell B7, start the VLOOKUP formula:
4. Click on cell A7 to enter its reference.
5. Type a comma, to end the first argument
6. Select the EmpMaster sheet
7. Select cells A2:C5 (the Employee list)
8. Press the F4 key, to change the cell references to absolute. The formula should now look like this:
9. Type a comma to end the second argument.
10. Type a 2, which is the column in the lookup table that contains the Emp name.
11. Type a comma to end the third argument.
12. Type FALSE, to specify that an exact match for the Ecode is found, and add the closing bracket.
13. The formula should now look like this:
14. Press the Enter key to complete the formula. The Emp Name will be displayed.
Note: To return the salary(see picture below), create a VLOOKUP formula that refers to column 3 of the lookup table. For example, enter the following formula in cell C7:
Now, you can select the B7 in Dept worksheet and drag it down and the VLookup formula will be copied. Now just fill the ECodes, the name and the salary will come automatically. and you are done.. voilla…
Yes yes you have just learned VLookup. Please put your comments, knowledge, suggestions, questions on this topic and any more interesting feature of Excel. Remember to Share!!!! Enjoy