How to use VLOOKUP in Excel 2013

VLOOKUP is probably one of the most useful function in Excel when it comes to looking up fields related to a variable. We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

The V in VLOOKUP stands for Vertical i.e. it looks up in vertical table. Since most of the real world data appears in vertical table, the VLOOKUP is one of the most used function in Excel. It works similar to human mind, first it searches for relevant key item and goes further in the row to get it's details.

Let's understand this better by most commonly encountered real world example of Employee details. Suppose a company has 10 employees with unique Employee ID followed by details like Name, Departement, Contact Number and E-mail ID.

If we need the Contact Number corresponding to Employee ID 201309 we first search the row in which this ID exists and then we move further in the same row to locate Contact Number. That's exactly how Excel also works, we just need to feed the data for what we are looking and from where we need to look.

Syntax:      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Insert the value to search in the first column of range or table. This value could be a value or it could be a cell reference (more dynamic). This helps excel to identify the row in which our data exists.
• table_array: The entire range of cells that contains data. We can directly use the range reference or a range name. This tells excel the location for search.
• col_index_num: The column in range selected under table_array corresponding to desired matching value field. (eg. Contact Number lies in column 4, hence 4 should be inserted)
• range_lookup: Optional field. Specifies whether we need exact match or approximate match. FALSE (or 0) corresponds to exact match while TRUE (or 1) corresponds to approximate match. The default value is FALSE (or 0) i.e. exact match.

IMPORTANT NOTES:

• If col_index_num < 1, Excel gives #VALUE! error
• If col_index_num > number of columns in range, Excel gives #REF! error
• While searching for text values in the first column, ensure that any cell value does not contain leading spaces, trailing spaces or inconsistent quotation marks. It may give error or incorrect values in the output.
• While searching for number or date values in the first column, ensure that data is stored in appropriate format (not as text format)