What are the VLOOKUP functions:
VLOOKUP is one of the most useful and important functions in
Microsoft Excel. It is generally used to look at a particular value in large
data sheets where searching manually can be a burden.
The VLOOKUP function supports approximate and exact matches,
and wildcards (*?).
Meaning of VLOOKUP Function
"V" means "vertical"!
The word "vertical" means that it can be used to
view the value vertically, so it can be used to view the value within a column.
Definition of Excel VLOOKUP
VLOOKUP can be defined as a function according to Microsoft
Excel, "which looks for a value in the left column of the table, and then
returns that value in the same row from the column you specified."
By default the table should be sorted in ascending orders.
VLOOKUP is one of the most useful functions of Excel, and
therefore it is important to understand it.
Syntax of VLOOKUP Function
VLOOKUP's syntax is composed of four kinds of information –
=VLOOKUP (value, table, col_index, [range_lookup])
This syntax contains arguments like this –
value – the first column of the table that you have to find.
table – the table from which to get this value. (Range in
which the value exists)
Remember that lookup value should always be in the first
column of the range, so that it can work correctly.
For example, if your lookup value is in cell C2, your range
should start with C.
col_index – the column of the table or range from which to
obtain the variety.
It has two options that are optional –
TRUE = Approximate Match and FALSE = Exact Match
If you don't specify anything, TRUE will be the default.
How Excel VLOOKUP Function Works:
Before we know how VLOOKUP is used, we should understand how
the VLOOKUP function works.
VLOOKUP is designed to retrieve the organization data in the
vertical rows table, where each row represents a new record.
If your data is horizontally organized, use the HLOOKUP
function.
How to use Excel VLOOKUP?
Before understanding how to use a vertical lookup function,
we should understand what its purpose is. Try to understand it with a sample
product.
Let's say we have a table as shown below.
Now always keep these three things in mind to use the
VLOOKUP function –
i) VLOOKUP Only Looks Right:
VLOOKUP only sees right and
VLOOKUP requires a table in which lookup values are in the
left-most columns.
The data you want to retrieve (the earliest value) can
appear in any column on the right:
When you use VLOOKUP, understand that each column in the
table is given a number from the left.
To get the value from a particular column, simply enter the
appropriate column number in "column index":
=VLOOKUP(H2,B2:E8,3,FALSE)
=VLOOKUP(H2,B2:E8,4,FALSE)
iii) VLOOKUP has two matching modes, exact and approximate:
VLOOKUP has two modes: matching: exact and approximate,
which is controlled by the fourth argument, called "range_lookup".
range_lookup return false to exact matching or true for
approximate matching.
True values in Excel are disused as Approximate Match"
and FALSE as "Exact Match".
IMPORTANT: the default of range_lookup is TRUE, so true will
be used by default when you enter nothing here.
=VLOOKUP(value, table, column) =VLOOKUP(value, table,
column, TRUE) =VLOOKUP(value, table, column, FALSE)
Example 1: Exact match
In most cases, you'll definitely want to use VLOOKUP in
exact match mode. This makes sense when you have a unique key to use as a
lookup value, for example, the title of Company Name in this data.
It is important to note that the lookup value should be
found in the text string "Apex Steel" lookup range. Except for case
(upper and lower), the value should be a perfect match.
If you type "Apex Steel" (with two specs) or type
"Apex Steel co" or "Apex comp", it won't match.
No extra spaces, abbreviations or characters. They should be
the same. This is called exact match.
Example 2: Approximate match:
When you select TRUE (Approximate Match), you ask Excel to
return the values that are almost identical to each other.
The best example is to find grades based on the student's
score. In this case you can get a best match from the lookup value value value
given from VLOOKUP.
In the example below, the D4 cell formula approximately gets
the correct grade from the match.
=VLOOKUP(C5,F4:G9,2,TRUE)
Note: Your data should be sorted in ascending order of
lookup value when you use vlookup's approximate match mode.
Example of VLOOKUP Function
Now we'll actually learn how VLOOKUP is used. For this, we
take an example of a computer shop in which the customer is billed. We will
create a product database for which the description and price of the product
will automatically be invoiced when you enter the item code into the bill,
making it easier to make the bill.
First, create a product database in a sheet.
Let us now create a sample invoice in the second sheet –
First enter the correct item code in cell A10.
Now click on the Insert Function (fx) button from the
Formulas tab to enter the formula in cell B10.
Type VLOOKUP here and then click Go.
Now the Function Arguments box will appear, in which we will
give all arguments (or parameters) required to complete the VLOOKUP function.
Now select the value in this way:
i) Lookup-value-
The function needs to indicate where to find the unique
identifier (item code in this case). So we have to select the cell in which we
entered the item code here (in cell A10).
ii) Table_array-
In other words, we need to tell VLOOKUP that the
database/database is not the same. Where to find the Litt. Click the selector
icon for the second argument.
Now the database/database is not the same. Select the litt –
which does not include the header line.
In our example, the database of stocks is on a separate
worksheet, so we will click on that worksheet tab first and then we will select
the entire database. Then press Enter.
iii) Col_index_num –
We will use this argument to specify vlOOKUP from the
database to the piece of information that is associated with our item code in
A10 and which we want returns.
In this particular example, we want the description of the
item to come in this cell, so the number of description columns in our product
database is two. So we will enter the "2" value in the Col_index_num
box.
iv) Range_lookup –
Finally, we need to decide whether to enter the value in the
final VLOOKUP argument, Range_lookup. Type FALSE here.
Now finally click on the OK button and see if the
description related to the item code "WD500" is entered correctly in
cell B10:
Similarly, we can enter a similar formula to return the
price of the item to cell D10. Note that the new formula should be created in
cell D10. It will look like this:
Note that in this formula, Col_index_num numbers have been
changed from "2" to "3", as the price we want to retrieve
is in the third column.
Now you can type formula like this in cell E10 for Total
=C10*D10
So this way you have to enter Qty in Item code and column C
in column A and the rest of the information will be automatically entered.
0 Comments