VLOOKUP is one of the most helpful and highly used functions to extract the required data from the dataset. The letter "V" represents vertical and "lookup" means looking for some value. VLOOKUP function vertically looks for a particular value that the user wants to look for in the dataset and populates the user required information corresponding to that lookup value.
Below we have explained the VLOOKUP function with an example of how we can use the VLOOKUP formula to extract the required information from a huge dataset.
How to use VLOOKUP Function in Excel
We have stock price data such as Stock Name, Closing Price, Price Change between 2 days, 52 Weeks Low and 52 Weeks High prices for 50 stocks under NIFTY 50.
Now suppose out of the 50 stocks we want to find out the stock price details for HDFCBANK. Without using any formula/function it might be a difficult and long manual process and almost nearly impossible if the data set goes in lacs of rows. We can use the function "VLOOKUP" in this situation and get the required data.
In cell G3, we have the stock name "HDFCBANK" and we want to populate the other information such as Closing Price, Price Change, 52 Week Low, and 52 Week High.
Simply applying the VLOOKUP formula in cell H3 to extract the price of HDFCBANK from the dataset.
=VLOOKUP(lookup_value, table_array, col_index_num, match_preference)
lookup_value = the value for which you want to extract the data from the dataset. [ in our case, look_value is HDFCBANK ]
table_array = the range of the dataset from which you want to extract the data. [ in our case, table_array is cell A2 to E52 ]
col_index_num = the column number of the dataset in which the data you want to extract is present. [ in our case, col_index_num is the price which is present in the second (2) column of the dataset ]
match_preference = Either you want to search the lookup_value (HDFCBANK) with the exact match or approximate match. For an exact match, specify 0 or FALSE in the formula and for the approximate match, specify 1 or TRUE in the formula - If the user specifies 1 for match_preference, then it may extract the data of HDFC first.
The formula for VLOOKUP, =VLOOKUP(G3,A2:E52,2,0) and then press Enter.
Similarly, we can apply the same VLOOKUP formula to get the Price Change (by changing col_index_num to 3), 52 Week Low (by changing col_index_num to 4), and 52 Week High (by changing col_index_num to 5).
Moving to the Dynamics of VLOOKUP
Now suppose, we want to extract the data for few more stocks (say AXISBANK, ICICIBANK, INDUSINDBK, KOTAKBANK & SBIN).
So far we have manually written the VLOOKUP function 4 times to get 4 different information and if we increase the number of required criteria (stocks), the number of times the formula to be used will also increase linearly. For every information required you got to use the formula separately, changing col_index_num. Also, you cannot simply copy and paste or drag the formula downwards as the table_array i.e. the dataset range will also move downwards which will lead to initial data falling out of dataset range.
Basic Rule to fix the Cell/Range in Excel
Whenever we want to copy and paste the formula to other cells (formulas that contains a reference to another cell/range), one thing that has to be kept in mind is that- whether we want to move the reference cell or range contained in the formula to other cell/range. If not, then we have to fix the cells so as to copy and paste the formulas to other cells. Basic Rule for Absolute and Relative Cell Reference in Excel are-
To fix the column - put the Dollar ($) sign before the Column Alphabet. [ $G3 ]
To fix the row - put the Dollar ($) sign before the Row Number. [ G$3 ]
To fix both column and row - put the Dollar ($) sign before both Column Alphabet & Row Number. [ $G$3 ]
For lookup_value, we want to value (i.e., the reference to the Stock Name) should be changing row (3,4,5,6,7,8) as we copy the VLOOKUP formula downwards and should be kept fixed column (G) as we copy the VLOOKUP formula to the right.
For table_array, we also need to fix the range (i.e., the reference to the dataset table) either-
Use the above rule to fix both Column Alphabet and Row Number in-place while defining table_array [ $A$2:$E$52 ], or
Assign a Name to the dataset range (for example, "StockInfoDataset") in the Name Manager and then StockInfoDataset can be used in-place while defining table_array.
For col_index_num, should be dynamic i.e., instead of changing the col_index_num for Price Change (by changing col_index_num to 3), 52 Week Low (by changing col_index_num to 4), and 52 Week High (by changing col_index_num to 5), one can use either of the following-
In this way, we can fix the reference cell/range within the formula and such formula can easily be copied and paste special (formulas) to other cells instead of manually writing the formula for each cell.
Formula for VLOOKUP, =VLOOKUP($G3,$A$2:$E$52,COLUMNS($A$2:B$2),0) and then press Enter. Now, it makes it easy for us to copy and paste special (formulas) to other cells in the matrix (H3:K8) to populate the data.
Key Aspects while using VLOOKUP Function
The range used for table_array should start from the column in which the lookup_value is present. [ in our case, HDFCBANK is present in column A ]
Excel range always assigns the first column of the table_array as 1 for reference. So it makes it easy for the user to use these references in the functions/formulas. [ as we referred to column B as 2 in our formula for col_index_num to extract the Stock Price from the dataset table ]
Excel always looks at the right
Excel always looks at the lookup_value in the left-most column and finds the corresponding value on the right side with the help of col_index_num.
Excel always refer to match at first instance
If the first column of the table_array contains duplicate data, it shall refer to the value which matches lookup_value in the first instance.
Excel performs a case-insensitive lookup
Irrespective of the upper case or lower case value, excel lookup for the value.
#N/A Error in Excel
Multiple VLOOKUP Function in Excel
Multiple VLOOKUP functions can also be performed in a single cell in the case where multiple datasets are involved using Multiple VLOOKUP.