=VLookup( Lookup_Value, Table_Range, Column_Number, [Approx or Exact])
The value being looked-up.
This can be a value, a cell reference, or text.
The table that contains the look-up values.
VLOOKUP always looks in the leftmost column of the table
The Table_Range can be an absolute cell reference or a named range.
The column containing the value to be returned.
This is the number of columns to the right in the Table_Range.
The result will be in this column at the same row where the
| Approx or Exact Match
If FALSE , an exact match must be found.
If TRUE , the closest match (less than the lookup_value) is selected.
Data must be in order if TRUE is selected.
Here is a list of Branch Numbers with their May 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 D9:E14 .
In column C row 2 we can place the following lookup formula:
=VLookup(A2, $D$9:$E$14, 2, FALSE)
The lookup values are the values A2, A3, etc. These reference the values being "looked up"
Notice the dollar signs around the D9:E14 table_range. This is an absolute reference to the branch name table range. The table will always be in this location.
We could assign a name to the range $D$9:$E$14. If we named it "Branch" then the formula would be
=VLookup(A2, Branch, 2, FALSE)
The results are displayed below:
Here ' 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