VLOOKUP Function in Excel with Example

In this Lesson, we will learn how to use VLOOKUP in Excel:

In Microsoft Excel, VLOOKUP is one of the most useful and well known excel function that allow us to search and retrieve data from one column to another location. Before start using this function, we would like to discuss, what is VLOOKUP? and what is the formula of VLOOKUP in Excel?

What is VLOOKUP?

VLOOKUP function is a very useful function in Excel, which help us to find and extract cell value from one column to another column in the same row. 

According to Microsoft, The VLOOKUP function say to Excel: 

=VLOOKUP(What you would like to look up - look for this piece of data/information like apple, where you want to look for it - in this data set/range, and tell me some corresponding data value about it - like the selling price of apple).

VLOOKUP Formula:

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

According to Microsoft, VLOOKUP function use the following arguments as mentioned below:

1. Lookup_value: The value that you want to lookup.
2. Table_array: table range in which you are looking for the value.
3. Col_index: specifying the column number of table range from which you want to fetch the matching value.
4. Range_lookup: specify whether you want an exact match (False - if an exact match not found, then it will return an error) or an appropriate match (True - if an exact match is not found, use the closest match below the lookup_value).

How to use VLOOKUP in Excel?

To provide you better understanding, we would like to share with you an examples as mentioned below:

• How to Find Closing Stock of Electronic Items Using VLOOKUP Function and How to Create Drop-Down List as Lookup value.

Example - Find Closing Stock of Electronic Items Using VLOOKUP Function: 

Suppose that you are working as a assistant inventory manager and your manager need to know that what is the closing stock of some electronic items on urgent basis for re-order of electronic items.

By following below mentioned steps you can easily find out your Lookup value:

Step 1: First Step is to organize the data by creating the inventory management sheet as mentioned below:

Step 2: Now the next step is to create "Item Description" and "Closing Stock" column in Cell "C18" and "D18" as mentioned in below snapshot:

Step 3: Now the next step is to type your Item Description in Cell "C19" for which you want closing Stock quantity as mentioned below:

Step 4: Now the final step is to insert the below mentioned formula in Cell "D19", to find out the closing stock quantity of "Table Lamp":

=VLOOKUP(C19, B6:H15, 7, 0)

Now you can see the VLOOKUP function working properly, you can also change the item description to find closing stock quantity of another electronic item. 

How to Create Drop-Down List as Lookup Value?

By following below mentioned steps, you can easily create drop down list for above mentioned example:

Step 1: Select Cell "C19" and Remove the Item Description as mentioned below:

Step 2: Now next step is to select Cell "C19" and click on Data menu tab:

Step 3:  Now in Data Tools group, click on Data validation drop down menu and select Data validation to open Data Validation Window as mentioned in below snapshot:

Step 4: Now in Data Validation window, click on drop down menu button of validation criteria and select the "List" from drop down menu as mentioned in below mentioned snapshot:

Step 5: Now you can see that new Source field has been shown on Data Validation window, now click on Source field button as mentioned below:

Step 6: After Clicking on Source field button, select the range of Cell from "B6" to "B15" for creating the drop down list as mentioned below:

Step 7: Now again click on Source field button and click OK to close the Data validation window as attached below:

Now you can see that drop down menu has been created, you can select your desired item description using drop down list to find out the closing stock quantity. 

Now you can see that your VLOOKUP function and drop down list both functions are working properly. snapshot attached below:

We hope that you will like this lesson.

Example: How to Create Unit Converter in Excel by Using "VLOOKUP Function" and "Convert Function".

---Thank You---