VLOOKUP Function in Excel

What is the VLOOKUP function in Excel?

→ The VLOOKUP function works as a vertical lookup by searching for a value in the first column of a table array and returning us the value available in the same row and defined in the column (index_number position).

→ This function works in a table organized vertically. V stands for Vertical in VLOOKUP.

→ It is a built-in function of MS Excel and it is very popular among all.

→ We can put it into a Lookup or Reference Function category.

➨ How to use the VLOOKUP function in Excel?

→ Refer below the article for the VLOOKUP function explained with examples.

→ We need to enter a simple formula in the MS Excel spreadsheet for getting the value from the table.

What is the syntax of the VLOOKUP function?

→ The syntax for the VLOOKUP function in Microsoft Excel is:

= VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])

The Syntax Parameter or Syntax Arguments are:

→ lookup_value – This is the value that should be available in the first column of a table array and also we are searching this value in the 1st column of the table. In other words, what you want to lookup?

→ table_array – The table from which we need to pull out the data. In easy language, we can say that from where you want to lookup?

→ col_index_number - The column in the table from which we need to pull out the data after comparing the lookup value in the 1st column of the table.

→ [range_lookup] – Range_Lookup helps us for either we need to pull out data with an exact match or approximate match. We have two values for that (1) TRUE = approximate match or we can add value as “1” (one), and (2) FALSE = exact match or we can add value as “0” (zero).

Important Points for using the VLOOKUP function

→ We can use the VLOOKUP function with any datatype such as a string, number, date, etc. 

 

→ If we mention "FALSE" or "0" for the approximate_match in range_lookup parameter and there is no exact match found, then the VLOOKUP function will return #N/A.

 

→ If we mention "TRUE" or "1" for the approximate_match in the range_lookup parameter and no exact match is found, then the next smaller value is returned.

 

→ If we specify the value of an index_number is less than 1 then the VLOOKUP function will return #VALUE!.

 

→ If we specify the value of an index_number is greater than the number of columns are available in the table then the VLOOKUP function will return #REF!.

Examples of VLOOKUP Function:

→ Now we are taking on an example of a stock sheet of a supermart.

→ So in the picture, the stocks and prices of various fruits are mentioned in the table.

→ We need stock and price of specific fruit mentioned in cell no D2. Here we have mentioned the “Orange” in cell D2.

→ So we will apply the VLOOKUP formula for this as we know the syntax of this function as mentioned below.

 

= VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])

→ lookup_value – in this example our lookup value is cell no D2 because we want the price and stock of fruit that is mentioned in cell D2.

→ table_array – Here our table array is A1:C6 – this is the whole table from this table we want to pull out the value of stock and price.

→ col_index_number – For the stock purpose, we will mention the column index number is 2 because in this table the stock column is the 2nd column. In the same way for the price column, we will mention the 3rd column.

→ [range_lookup] – This is used for the approximate match or exact match, in this example, we want the exact match value from the table with cell D2. So we will mention the “0” or False in the formula.

→ Now, Our formula for the stock of Orange =VLOOKUP(D2,A1:C6,2,0) while we have to write Orange in cell D2. So this formula will give us the stock of orange = 90 kg.

→ Similarly, we can get the price just changing the column index number 3 instead of 2. So the formula for the price of orange =VLOOKUP(D2,A1:C6,3,0). So this formula will give us the price of orange = 7$.

 

👉 For a regular update:

➨ Join us (Telegram Channel)

➨ Join us (LinkedIn Page)

👉 Also Navigate Our Popular Category:

➨ Statistical Functions

➨ Date & Time Functions

➨ Logical Functions

➨ Lookup & Reference Functions

➨ Math Functions

➨ MS Excel Sitemap

➨ 7 QC Tools Excel Templates

➨ Lean Six Sigma Excel Templates

➨ MS Excel Templates