Be A Master In Using VLOOKUP Function

While working with large number of data it’s really a difficult task and time-consuming process to search data related to a specific field and use it for another purpose. We can use VLOOKUP function in such condition to fetch data related to a particular field from large number of records.

This function can be used effectively in following circumstances.

When an organization has entire details about employees in a worksheet, it is possible to fetch available related data in the worksheet about any employee. Based on employee name or employee ID, VLOOKUP can fetch other existing details like age, date of joining, designation, experience, salary etc.

If a library has the entire details about books, it is possible to fetch existing related data about any book. Based on book name or book ID, VLOOKUP can fetch other existing details like author name, published date, location, member ID to whom the book was issued, issued date etc.

If a Company has entire details about products, it is possible to fetch existing related data about any product. Based on product name product ID, VLOOKUP fetches other details like dimension details, manufacturing details, stock details, price details etc.

The value fetched by VLOOKUP can be effectively used to do another task. If you change the source details, VLOOKUP automatically replaces it over the old value.

Let us discuss VLOOKUP briefly with an example. Please find below picture in which a small part of worksheet is displayed. There are thousands of rows and few columns have details about a product and the range of cells we see in this page is only between A1 to E5.

We are going to fetch price details of a particular product without searching manually and pasting the value.

Syntax for VLOOKUP

=VLOOKUP(KEY FIELD, SOURCE TABLE, COLUMN ID, ACCURACY)

Here key field is the matching data to be searched. Source table is the database. Column ID is the reference column right from the matching key field; Accuracy is the level of accuracy need.

Let us find the price details for a given product in the below example. The format is simple and made with an objective that once we type any Product ID in the cell M9, Excel has to search automatically its price, display the price value and further consider the value to calculate with number of units.

Please look at the syntax here

=VLOOKUP(M9,$A$1:$E$5,4,FALSE)

M9 is the key field to be searched. $A$1:$E$5 is the source table. The number 4 is the column index just right from the key field column. False is the accuracy level.

Here false is meant for accurate matching of M9 in the source table. If true is mentioned then Excel finds a value that is approximate matching to M9. (Choose false by default)

Column Index is based on source table (1=CODE, 2=MODEL, 3=MAKE, 4=PRICE and 5=SIZE). The column index we given 4 is meant for price.

Based on M9 value, the result O9 returns value. Check the right side picture, where the code given is ‘10000288’. Excel searches exact matching word in the source table ($A$1:$E$5) and get the 4^th column index value ($4,999.00) in the cell O9.

Fetched value is the price of single unit and P9 is the price of number of units given. You can copy and paste the formula in the below cells to get more product prices.

Tips: Insert $ symbols in source table cell range. This keeps the range of cells unchanged while dragging or pasting into another cell.

Advanced method:

If key field is blank, VLOOKUP returns error message (#N/A). To avoid displaying such messages please change the formula a slightly using IF function. (To know more about IF FUNCTION please go through archives)

Following is the modified syntax

=IF(M9<>0,VLOOKUP(M9,$A1:$E$5,4,FALSE),””)

In case M9 equals zero, IF function returns to blank. Otherwise VLOOKUP will be carried out and excel fetches price value.

=IF(N9<>0,N9*O9,””)

In case the number of product N9 is zero, IF function returns to blank. Else calculates price value for number of products.