VLOOKUP in Excel can be used to automatically populate values from a given list based for specified identifier. For example you can use VLOOKUP to pre-populate employee code based on employee name. Let us take the following example where the employee list (name, code) is defined in the range A2:B6
Now we can use VLOOKUP automatically populate the employee code in column G based on employee name F.
lookup_value – The value for which corresponding entry needs to be found out.
table_array – The list that needs to be used for picking the values.
col_index_num – Index of the column that needs to be retrieved.
range_lookup – Use this for matching range of values.
So to retrieve the employee code for employee name in F2, the VLOOKUP would be as shown in the below screenshot.
lookup_value (F2) – Name for which the employee code needs to be retrieved.
table_array (A2:B6) – The list containing employee code and employee name.
col_index_num (2) – Column index of employee code that needs to be retrieved.
range_lookup (FALSE) – The value is set to false as we need exact match.
You can use the auto fill feature to copy the formula for cell G3:G6. The value for G3:G6 cells are displayed as #N/A as we do not have any corresponding entries for employee name in column F3:F6. The #N/A error can be fixed using the IF command along with VLOOKUP
Now if you enter the name “Srinivasan” in F3, the corresponding employee code will be populated in G3.