VLOOKUP function in Mircrosoft Excel explained with an example

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

Define table array in Excel

Now we can use VLOOKUP automatically populate the employee code in column G based on employee name F.

VLOOKUP in Microsoft Excel

=VLOOKUP(lookup_value, table_array,col_index_num,[range_lookup])

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.

VLOOKUP explained with an example

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.

201404161322.jpg

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

=IFERROR(VLOOKUP(F2,A2:B6,2,FALSE),””)

201404161325.jpg

Now if you enter the name “Srinivasan” in F3, the corresponding employee code will be populated in G3.

Also See: Display empty value instead of zero in Microsoft Excel

In Category: Excel 2007, Excel 2010, Excel 2013, MS Office

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 0 Comments
No comments yet. Be the first.

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.