# VLOOKUP Function: A Step-by-Step Tutorial

This tutorial explains the VLOOKUP function in layman's language and how to use it in Excel, along with examples.

## VLOOKUP: Introduction

The Vlookup function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.

## Syntax of VLOOKUP

The syntax for Vlookup function is as follows :

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)
VLOOKUP Function Explained in Plain English
VLOOKUP (value to be found, table reference, pull a matching value from the Kth column of the table , exact match / closest match)

Detailed Explanation
1. lookup_value - It is the value to be found in the first column of the table.
2. table_array - It is a table of text, numbers or logical values. Table_array can be a cell reference or a range name.
3. col_index_num - It is the column number in table_array from which the matching value should be returned.
4. range_lookup - 1 (TRUE) - Find the closest match in first column sorted in ascending order. 0 (FALSE) - Find an exact match.
Note : If you don't enter a value in the fourth parameter "range_lookup" of vlookup, excel will consider 1 as a logical value i.e. closest match.

Example

Suppose you have data for students' names and their classes and sections. You want to find the class name for a student in the data table.

used in the examples shown below and practice what you learn.

The data is shown in the image below :

`=VLOOKUP(B12, \$B\$3:\$E\$8, 3, 0)`
The detailed explanation is shown in the image below :

In this case, Excel will search for the value in B12 within the range B3:E8. If it finds an exact match, it will return the value from column D. In the formula, 3 refers to the third column of the specified range B3:E8 which is column D.

Limitation of VLOOKUP
To make VLOOKUP work, you need to have a lookup value in the first column of the table. If your look up value is not placed in the first column of the table, it will return #N/A error.

Look up value must be in the leftmost column of a table

Suppose you want to do a dynamic column lookup based on the value in cell B11. Manually you need to change the formula in each cell (C11 through E11) to update the third parameter of Vlookup formula (col_index_num). Look at the image below.

Use the COLUMN function to increment column index number automatically by 1
`=VLOOKUP(\$B11, \$B\$3:\$E\$8, COLUMN(B1), 0)`
How COLUMN function works?

It returns the column number of a cell reference.

For example, the formula =COLUMN(B1) returns 2, because column B is the second column.

After applying the formula in cell C11, paste the formula to the right (Select the cells C11 through E11 and press CTRL + R).

As shown in the image below, you can change the column from which you want to extract data by copying the formula to the right and the VLOOKUP will adjust accordingly.

## Dynamic Vlookup Function

Suppose columns are not ordered in the table the way you want values to be returned.

Since the column index numbers are not in any proper order, we cannot use COLUMN function.
Instead we can use MATCH function to return the column index number.

`=VLOOKUP(\$B11, \$B\$3:\$E\$8, MATCH(C10,\$B\$2:\$E\$2,0),0)`

In this formula, we are using VLOOKUP with the MATCH function to dynamically select a column based on the value in cell C10 and pull data from the specified column in the range \$B\$3:\$E\$8.

How MATCH function works?
The MATCH function returns the relative position of an item in an array that matches a specified value.
= MATCH(value to be found, range of cells being searched, match type)

After applying the formula in cell C11, paste the formula to the right (Select the cells C11 through E11 and press CTRL + R).
= MATCH (C10, \$B\$2:\$E2,0) evaluates to :
= MATCH("Last Name", ("First Name","Last Name","Class","Sections"), Find the closest match)
Result : It returns 2 as "Last Name" is placed second in list.

Related Posts
Share

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 7 Responses to "VLOOKUP Function: A Step-by-Step Tutorial"
1. Can you please explain how to find duplicate value in two different sheets using Vlookup and IFERROR . It will be of great help . And congratulation for such a wonderful site on Excel, SAS and analytics all together .