Ad

October 23, 2017

How to Use VLOOKUP Function in Excel

October 23, 2017
When you need to find things in a table, the Excel VLOOKUP function is a great solution. It is easy to use. The ‘V’ in VLOOKUP goes for vertical, which means the table must be arranged vertically with data in rows. When finding a value from a table, VLOOKUP does not differentiate upper and lower text. For example, a name, ‘AHMED’ is identical to ‘ahmed’. When you are using VLOOKUP, assume that every column in the table is numbered, starting from the left. It is necessary because when you need to retrieve a value from a table you will need a column number. Let’s see the syntax and some examples.

Syntax:

=VLOOKUP(value, table, column, [range_lookup])

Arguments:

value: (required) The value you want to look up from the first column of a table.

table: (required) The range of cells in which the VLOOKUP will return the value.

column: (required) The column number starting from 1 with left column of a table.

range_lookup: (optional) True or False. True is approximate match (default). False is exact match.

Example - Finding Employees Data Using VLOOKUP

Imagine you have large amount of employee’s data which is structured in Excel table and you need to search for a specific employee’s information.  For this you can apply VLOOKUP function. First you will have to draw a small table where you can apply function to retrieve the specific data from the large table. Let’s see how to work on it.

VLOOKUP function: Finding employees data in a table

The formula in cell C7 is:

=VLOOKUP(C6,B13:F22,2)

You can see from the above table that function retrieved the ID 6 information when I typed 6 in ID cell C6.  This is very simple example and little amount of data. In organization you may have large amount of data which you can easily handle by the same method.

When you use VLOOKUP function in excel, just apply the function in one cell and drag it to other cells in order to apply the same function to them and save your time. When you drag the function to other cells, you need to keep the value and range same and just change the column numbers as shown in the above approximate example.

Approximate and Exact Match

In the above example I used approximate match which is default (TRUE). If you want to use the exact match (FALSE), just write FALSE after column number with comma separated. When you apply exact match mode then you must write the exact value to retrieve the result. If you write the wrong value then you will get an error. See the given below example.

Excel formula: VLOOKUP function

You can see from the above table that function returned an error #N/A when I wrongly typed 66 in ID cell C6. It is because I used exact match. If I used approximate match, I wouldn’t get the error, I would get the most possible result. Because in approximate match mode, when VLOOKUP finds a value that’s greater than the lookup value, it goes back and returns a value from the previous row. Let’s give some look to the approximate match.

Excel formula: VLOOKUP function

The function looks down row by row to decide which row to stop on. It continues looks down until it finds a value that is greater than the lookup value, and then it stops on the previous row.

In case if the value does not exist in the first column of a table, then VLOOKUP returns a result from the last row. See the given below example.

Excel formula: VLOOKUP function

As you can see the VLOOKUP function returned the last row value because I wrote 25000 sales which does not exist in the table.  This is the difference of approximate and exact match.

When you apply approximate match, you must sort your values in ascending order in the first column of a table, otherwise you will get an error. While there is no such rule for the exact match. You may sort your values both in ascending and descending order in the first column of a table.  

Do practice on VLOOKUP function. If you face any problem while in practice, please do comment in the given below comment section. I will reply you as soon as possible.
Share:

0 comments:

Post a Comment