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.
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
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.
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.
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.
0 comments:
Post a Comment