VLookup Worksheet Function
Printer Friendly Page
VLOOKUP finds a specific row in a table of data. It then looks for a value in the specified column number of the table and then returns the found value.
SYNTAX:
=VLookup( Lookup_Value, Table_Range, Column_Number, [Approx or Exact])
| Lookup Value |
- The value being looked-up.
- This can be a value, a cell reference, or text.
|
| Table Range |
- Table that contains the look-up values.
- VLOOKUP always looks in the leftmost column of the table for the matching value.
- The Table_Range can be an absolute cell reference or a named range.
|
| Column_Number |
- The column containing the value to be returned.
- This is the number of columns to the right in Table_Range.
- The result will be in this column at the same row where the lookup value was found.
|
Approx or Exact Match
[OPTIONAL] |
- If FALSE, an exact match must be found.
- If TRUE or blank, the closest match (less than the lookup_value) is selected.
- Data must be in order if FALSE is selected.
|
EXAMPLE:
Here is a list of
Branch Numbers with their
Quarterly Sales Numbers:
We want to put the appropriate Branch Name in
column C.
Notice that there is a table of branch numbers with their names at the range
D8:E12.
In
column C we can place the following lookup formula:
=VLookup(A1, $D$8:$E$12, 2)
Notice the dollar signs around the
D8:E12 range. This is an absolute reference to the branch name table location.
We could assign a name to the range D8:E12. If we named it
"Branch" then the formula would be
=VLookup(A1, Branch, 2)
The results are displayed below:
Here's what happened:
- In this example, the value in A1, which is 300,
is looked up in the leftmost column of the range D8:E12.
- 300 is found in D8.
- In column 2 of the found row of the table range, which is E8, is the value Boston.
- This is the value that will appear in cell C1
Another Example - Multiple Lookups of the Same Table-Array
In this example, we have the branch numbers and the June sales amounts in columns
A and
B.
The lookup table has a third column that contains the annual sales for each branch.
[E10:E15]
Create a Vlookup formula in column C.
(=VLOOKUP(A2, $C10:E15, 2).
This formula will search the leftmost column of the table range
($C$10:$E$15) for the value in A2
(300).
When the value 300 (A2) is found at row 11, the formula will look in the second
2 column of the table to get the branch name.
In column D, create a similar formula except the column number will be
3 instead of
2 because we want the annual sales for the selected branch.
(=VLOOKUP(A2, $C10:E15, 3)
The results of the formulas are shown below:
