VLOOKUP Excel Function

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:

1. Lookup_value: Is the value or id which has to be seared in a table
2. Table_array: Is the table or range in which Lookup_value would be searched
3. Col_index: Is the column number in the table from which the matching value must be returned. The first column is 1.
4. 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:

1. In general, you will find term lookup value is used in place of value to be looked up. Both are the same.
2. In the table reference or range in which the lookup value is located, the lookup value column should always be in the is the first column for VLOOKUP to work correctly. For VLOOKUP in an excel example, if the lookup value is in cell D2, then your range should start with D.
3. 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.
4. 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 is 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 the 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 the 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_matchparameter and if no exact match is found, then the 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, and else the Vlookup function will return #VALUE!.
• If the value of index_numberis greater than the number of columns in the reference table, the Vlookup function will return error #REF!.
• This function must be applied to 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 the VLOOKUP function along with VLOOKUP in excel example and downloadable excel templates. You may also look at these useful functions in excel –

• VLOOKUP in Power BI
• VLOOKUP Tutorial
• LOOKUP Formula in Excel
• Excel VLOOKUP Examples