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 :
In Layman's Terms
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 :
Limitation of VLOOKUP
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).
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.
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.
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.
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)
About Author:
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.
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 .
ReplyDeletevery helpful
ReplyDeleteHey Its really helpful to understand VlOOKUP. Thank you !
ReplyDeleteI 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 ?
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!
DeleteExcellent
ReplyDeleteVery useful and dimple explanation.
ReplyDeleteHi bro...Want to build VBA code for a user defined NthVlookup function which can provide me the vlookup value for nth occurence...
ReplyDelete