One of the fantastic feature of Microsoft Excel is VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) functions. It helps in the day-to-day office work. These functions are used to look for certain information in a large database. if the number of employees in a company is 50 it may be easy manually to look for any of their information like age, nationality, salary and department but difficult if more. The information may be saved in different files or sheets. VLOOKUP and HLOOKUP functions can be used to get the data from anywhere as per our requirement. It can be explained by an example.
The below first image shows the name of 10 employees with their salary which is in the first sheet and name of the employees with their age in the second sheet of the same file. if we have to insert the age of the employees from second sheet to the cell near to the salary of the employees in the first sheet we can use VLOOKUP function.
First keep the curson the cell where you want the data to be inserted from another field. Type =”VL” then excel will display “=VLOOKUP”. Double click on that then it will change to ” =VLOOKUP( “. Type the lookup_value (cell number of the employee name), table_array (range of the table from where we need the information), col_index_num (count the number of cells from the starting of table range), range_lookup (TRUE (approximate match) OR FALSE (exact match)))
The cell formula will be ” =VLOOKUP(B3,Sheet2!B:C,2,FALSE) “. In this lookup value is cell “B3”, the data is in a different sheet of the same file “sheet2”, “table array” is from “B to C”, col_index_num is “2” and range_lookup selected is “FALSE” for exact match. If there is an error in the formula then the cell will show as “#NAME? or #VALUE! or #REF! or #N/A” after pressing enter key. Copy and paste the formula in all the below columns to insert information in the remaining columns.
Once we brought the information from a different cell the formula in the cell can be erased by copying the enter result and “paste special” as “values”. The data can also be copied and pasted to Microsoft Word to prepare any document files and Microsoft PowerPoint for presentation.
Using the above method we can also get information from different cells of the same sheet and from any of the sheets of a different file. Using VLOOKUP we can get information from columns only and HLOOKUP is used for getting information from raw. A very important thing to note that VLOOKUP will not work if there is any merged cells. Make sure that number and data are not stored in text format. The result will show incorrect if any of the cells are in wrong format. There should not be any erroneous character in any of the cells for correct result.
Very small and medium size business establishments cannot afford to pay for customized software packages are using Microsoft Excel to maintain their human resource and finance records. VLOOKUP and HLOOKUP is an important and useful function of Excel to handle their data very easily and effectively.
By doing many exercises of VLOOKUP and HLOOKUP you will be able to understand the possible errors than can occur and how it can be corrected.
VLOOKUP and HLOOKUP explained below in a video presentation by an another example.