How to Use VLOOKUP in Microsoft Excel (Free PDF Guide Inside)

What is VLOOKUP function?

You can use the VLOOKUP function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees’ ID numbers are stored in the first column of the range. See example below.

If you know the employee’s ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value (“Axel Delgado”).

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find. Following is the syntax of vlookup: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

How to use VLOOKUP in MS Excel

Here is step by step guide to use vlookup in MS Excel:

Let us assume that you have a list of countries where you need to find capital against these countries and you already have a sheet from which the information has to be pulled.

1.Click fx button to insert function

2.Formula box will display and most recently used formulae will be visible

3.In search box, start typing vlookup

4.The vlookup function will be visible, select and click OK.

5.This will open up the vlookup function

 

 

 

 

 

 

 

 

 

 

6. In the vlookup function argument box, click vlookup_value and select cell in the excel sheet against which the information is sought

Example – Here we wish to get the capitals of various countries from an available database.

 

 

 

 

 

 

 

 

 

7. Click in Table_array box and go to the sheet or file which contains the database.

Example – Here in database file we already have countries, their capital cities and other information in various columns.

 

 

 

 

 

 

 

 

 

8. Select all columns between the lookup value and the column from which the data is to be populated.

9. Click in Col_index_num and enter the column number of the column from where the data is to be retrieved

Example – here we need to retrieve the capital of countries and the column number which contains the information is 3.

 

 

 

 

 

 

 

 

 

10. Click Range_lookup and enter “0” (zero). (zero for exact value and 1 for best match). Click OK.

If you need to replace the formula results with actual values, then simply copy the results > right click and paste as “values”

 

 

 

 

 

 

 

 

 

 

Hope you find this article useful for your daily routine in office. You may download PDF Guide from below link:

Please let us know in comments below if you are facing any problem. We will be more than happy to assist you.

Recommended: Best alternative to Adobe Illustrator and Best alternative to Adobe Photoshop.