VLOOKUP Function and Its Uses

Explanation

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

The syntax for Vlookup function is as follows :
VLOOKUP (lookup_value, table_array, col_index_num ,range_lookup)

In Layman's Terms

VLOOKUP (value to be found, table reference, pull a matching value from the Kth column of the table , exact match / closest match)

Detailed Explanation

lookup_value - It is the value to be found in the first column of the table.

table_array - It is a table of text, numbers or logical values. Table_array can be a cell reference or a range name.

col_index_num - It is the column number in table_array from which the matching value should be returned.

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.

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

The data is shown in the image below :



The detailed explanation is shown in the image below :



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


Semi Dynamic Vlookup Formula

Look at the image below. We need to change the formula in each cell (C11 through E11) to update the third parameter of Vlookup formula (col_index_num).


Use the COLUMN function to increment column index number automatically by 1

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



Dynamic Vlookup Formula

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.


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.

Download the workbook 
ListenData Logo
Spread the Word!
Share
Related Posts
About Author:
Deepanshu Bhalla

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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

7 Responses to "VLOOKUP Function and Its Uses"
  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 .

    ReplyDelete
  2. Hey Its really helpful to understand VlOOKUP. Thank you !
    I have one doubt, In the Section how column works ? You have mention "Select Cells C11 through C13 and then press CTRL+R " but i think it should be "Select Cells C11 through E13 and then press CTRL+R". But on doing that, The counter value increases but "Look up value" changes and hence it shows N/A. And to fix that we need to manually change to the look up Value. Can you please make this understand ?

    ReplyDelete
    Replies
    1. It should be cell C11 through E11 and then Press CTRL R. I have corrected it in the article. You can fix the reference of the cell column so that lookup value does not change automatically. For example, cell reference of lookup value is B11 so you should use $B11 in the first parameter of VLOOKUP (Mind the $ Sign before B). Hope it helps!

      Delete
  3. Very useful and dimple explanation.

    ReplyDelete
  4. Hi bro...Want to build VBA code for a user defined NthVlookup function which can provide me the vlookup value for nth occurence...

    ReplyDelete

Next → ← Prev
Looks like you are using an ad blocker!

To continue reading you need to turnoff adblocker and refresh the page. We rely on advertising to help fund our site. Please whitelist us if you enjoy our content.