=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 |
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. |
Column_Number | 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 [OPTIONAL] |
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