VLOOKUP Excel Function (Formula, Examples)

Excel Functions Tutorials

VLOOKUP Excel Function (Table of Contents)

VLOOKUP Function in Excel
VLOOKUP Formula
How to do VLOOKUP in Excel?

VLOOKUP Function in Excel

While working with two or more datasets we come across a situation where the need is to compare or interlink the datasets. Also, some time objective could be to pull the matching or corresponding data from the certain dataset for the set of identifiers. In excel this problem can be solved using various functions like VLOOKUP, Index, Match, IF etc. Where VLOOKUP Excel Function is the easiest and extensively used to solve any excel problem.

When the VLOOKUP function in excel is called, the value to be looked up is searched in the leftmost column of the table array which has been passed as a reference in the VLOOKUP function excel. Once the lookup value is found it returns the corresponding value from the table array.

The V in VLOOKUP stands for vertical search (in a single column), while the H in HLOOKUP stands for horizontal search (within a single row).

The VLOOKUP Excel function is a built-in function in Microsoft Excel which has been categorized as a Lookup/Reference Function.

VLOOKUP Formula

The VLOOKUP Formula in Excel is as follows:

Explanation of VLOOKUP Function in Excel

VLOOKUP formula in excel accepts the following arguments:

Lookup_value: Is the value or id which has to be seared in a table
Table_array: Is the table or range in which Lookup_value would be searched
Col_index: Is the column number in the table from which the matching value must be returned. The first column is 1.
Range_lookup: [Optional]. If this parameter is omitted, it defaults to ‘1’. Range_lookup can accept the following parameter:

‘0’ or ‘FALSE’ for an exact match
‘1’ or ‘TRUE’ for an approximate match

How to use VLOOKUP in Excel?

Before getting started with the V-lookup function in Excel, go through the following key guidelines:

In general, you will find term lookup value being used in place of value to be looked up, both are same.
In the table reference or range in which the lookup value is located, the lookup value column should be always in the is the first column for VLOOKUP to work correctly. For VLOOKUP in excel example, if the lookup value is in cell D2 then your range should start with D.
You should count Column Number which you wish to return in the Vlookup formula in excel from the start of the table column. For VLOOKUP in excel example, if you specify C2: E5 as the range, you should count C as the first column, C as the second, and so on.
Optionally, you can input TRUE or ‘0’ if you want an approximate match or FALSE ‘1’ if you want an exact match of the return value. The default value of Vlookup will always be set to TRUE or approximate match.

Below is the Vlookup formula in excel after putting all of the above together:

=VLOOKUP(lookup value, the range or table reference containing the lookup value, the column number from the range to be returned, TRUE for an approximate match or FALSE for an exact match).

VLOOKUP in Excel Examples

Let’s take a few VLOOKUP in excel examples, before using the excel Vlookup function:

VLOOKUP in Excel Example #1

In the below spreadsheet we have an employee data. Our objective is to pull matching data for a set of employee ids which are present in the right-side table.

Inside VLOOKUP function in excel we have passed the required parameters. Steps,

select the lookup value which is the cell G4,

pass the table reference which is B4:D10,

the column number of the table to be returned here we need Email so it’s 3, and

match type as ‘0’ which means we need an exact match.

VLOOKUP formula in excel can be rewritten as =Vlookup(G9, $B$4:$D$10,3,0) which returns Gagan@yahoo.com.

If you are thinking what is the purpose of using $ sign in table reference then continue reading this. The $ sign is used to freeze the table reference using the function keyword F4 also, called absolute referencing. So, if you drag the VLOOKUP Excel formula to below cells the table reference would remain unchanged.

Note: For the lookup value = 169 there is none record with the same value in the Employee id column. Hence, the VLOOKUP formula in excel returns here error as #N/A. If nothing found it returns # N/A.

VLOOKUP in Excel Example #2

It is not necessary that lookup value must be a number it can be a string or a series of character.

In this VLOOKUP Function example, we would again use the same dataset but the lookup value is changed to an email address and in return, we would like to see the employee id for the provided email.

Inside VLOOKUP formula in excel we have passed the required parameters as following:

select the lookup value which is the cell H8 or from Column H,

pass the table reference which is B4:D10,

the column number of the table to be returned here we need Employee ID so it’s 3, and

match type as ‘0’ which means we need an exact match.

You have noticed that in the lookup dataset we have moved the Email column left side of the table. This is the limitation of Vlookup formula in excel that is the lookup value column must be on the left.

We can rewrite the VLOOKUP formula in excel =Vlookup(gagan@yahoo.com,B4:E10,2,0) this returns value 427.

VLOOKUP in Excel – Example #3

Now, you want to look up a gene ID in the given data and wants to see its function. The value (gene ID) to lookup is given in cell F4. To lookup the value and return its function, you can use the VLOOKUP Formula in Excel:

= VLOOKUP(F4, A3:C15, 3)

F4 – value to look up

A3:C15 – table array

3 – column index containing the required return value

It will return the function of the corresponding ID.

Things to Note About the VLOOKUP Function in Excel

The Excel Vlookup function can return any data type such as a numeric, date, string etc.
If you select FALSE for the approximate_matchparameter and if no exact match is found, then the excel Vlookup function will return #N/A.
If you select TRUE for the approximate_matchparameter and if no exact match is found, then the next smaller value is returned.
The index_numbermust is not less than 1, else the excel Vlookup function will return #VALUE!.
If the value of index_numberis greater than the number of columns in the reference table, the excel Vlookup function will return error #REF!.
The excel Vlookup function must be applied on a dataset which does not contain duplicate in lookup column of the table array. As excel Vlookup function returns the first record where value matches in a table.
If you’re looking for numbers in a list with numbers, you should make sure they are not formatted as text.

You can download this Excel VLOOKUP Function template here – VLOOKUP Excel Template

VLOOKUP Excel Function