=VLookup( Lookup_Value, Table_Range, Column_Number, [Approx or Exact])

#### VLOOKUP finds the data in a specific row and column within a table of data.

It looks for a given value in the first column of the table and then retrieves
values from other columns in the same row.

 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 for a value that matches the Lookup_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 the 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 , the closest match (less than the lookup_value) is selected.    Data must be in order if TRUE is selected.

EXAMPLE:

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

.

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: 