VLOOKUP is an extremely useful tool, and learning how to
use it is easier than we think!
The VLOOKUP function performs a vertical lookup by
searching for a value in the first column of a table and returning the value in
the same row in the index_number position. The VLOOKUP function is a built-in
function in Excel that is categorized as a Lookup/Reference Function.
Syntax
There are four pieces of
information that you will need in order to build the VLOOKUP syntax:
|
Value
|
The value you want to look up, also
called the lookup value.
|
|
Table
|
The range where the lookup value is
located. Remember that the lookup value should always be in the first column
in the range for VLOOKUP to work correctly. For example, if your lookup value
is in cell C2 then your range should start with C.
|
|
Col_index
|
The column number in the range that
contains the return value. For example, if you specify B2: D11 as the range,
you should count B as the first column, C as the second, and so on.
|
|
Range_lookup
|
Optionally, you can specify TRUE if
you want an approximate match or FALSE if you want an exact match of the
return value. If you don't specify anything, the default value will always be
TRUE or approximate match.
|
VLOOKUP retrieves data based on column index
When you use VLOOKUP, imagine that every column in the
table is numbered, starting from the left. To get a value from a particular
column, simply supply the appropriate number as the "column index":
Formulae
=VLOOKUP(H3,B4:E13,2,FALSE) // first
=VLOOKUP(H3,B4:E13,3,FALSE) // last
=VLOOKUP(H3,B4:E13,4,FALSE) // email
1. H3
is lookup_value, or the value you want to look up.
2. B4
to E13 is table_array, or the range where the lookup value is located.
3. 2,3
and 4 is col_index_num, or the column number in table_array that contains the
return value.
4. FALSE
is range_lookup, so the return value will be an exact match.
5. Output
of the VLOOKUP formula is Jonathan if the col_index_num is 2, and so on.
Example1
Example2
Example3
Example4
Example5
VLOOKUP has two matching modes, exact and approximate
VLOOKUP has two modes of matching: exact and approximate,
which are controlled by the 4th argument, called "range_lookup". Set
range_lookup to FALSE to force exact matching, and TRUE for approximate
matching.
Important: range_lookup
defaults to TRUE, so VLOOKUP will use approximate matching by default:
=VLOOKUP(value, table, column) // default, approximate
match
=VLOOKUP(value, table, column, TRUE) // approximate match
=VLOOKUP(value, table, column, FALSE) // exact match
Example 1: Exact match
In most cases, you'll probably 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 movie title in this data:
The formula in H6 to lookup year based on an exact match
of movie title is:
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match
Example 2: Approximate match
You'll want to use approximate mode in cases when you're
looking for the best match, not an exact match. A classic example is finding
the right commission rate based on a monthly sales number. In this case, you
want VLOOKUP to get you the best match for a given lookup value. In the example
below, the formula in D5 performs an approximate match to retrieve the correct
commission.
Your data must be
sorted in ascending order by lookup value when you use approximate match mode
with VLOOKUP.
Notes
·
VLOOKUP
supports approximate and exact matching, and wildcards (* ?) for partial
matches.
·
The "V" stands for
"vertical".
·
Lookup values must appear in the first column of
the table, with lookup columns to the right.
·
If you specify FALSE for the approximate_match
parameter and no exact match is found, then the VLOOKUP function will return
#N/A.
·
If you specify TRUE for the approximate_match
parameter and no exact match is found, then the next smaller value is returned.
·
If col_index is less than 1, the VLOOKUP
function will return #VALUE!.
·
If col_index is greater than the number of
columns in table, the VLOOKUP function will return #REF!
No comments:
Post a Comment