## VLOOKUP Function in Excel

Vlookup excel function is an inbuilt referencing function which is used to find out a certain data from a group of data or range which is also known as table array, vlookup formula uses total of four arguments, the first argument is the reference cell and the second argument is the table array, the third argument is the column number where our data is and fourth the matching criteria.

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 objectives could be to pull the matching or corresponding data from a 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 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

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 the 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).

### Examples

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

#### Example #1

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

Inside the 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.

#### Example #2

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

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.

#### Example #3

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

**= VLOOKUP(F4, A3: C15, 3)**

**F4** – value to lookup

**A3: C15** – table array

**3** – column index containing the required return value

It will return the function of the corresponding ID.

**Things to Note**

- This function can return any data type such as numeric, date, string, etc.
- If you select FALSE for the
*approximate_match*parameter and if no exact match is found, then the Vlookup function will return #N/A. - If you select TRUE for the
*approximate_match*parameter and if no exact match is found, then the next smaller value is returned. - The
*index_number*must is not less than 1, else the Vlookup function will return #VALUE!. - If the value of
*index_number*is greater than the number of columns in the reference*table*, the Vlookup function will return error #REF!. - This function must be applied on a dataset that does not contain duplicate in the lookup column of the table array. Vlookup function returns the first record where the 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.

### Recommended Articles

This has been a guide to VLOOKUP Function in Excel. Here we discuss the VLOOKUP Formula in excel and how to use VLOOKUP function along with VLOOKUP in excel example and downloadable excel templates. You may also look at these useful functions in excel –

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion