VLookUp Function

by Linda Quinn

Close Window        Print Page


=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 .

vlookup



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)


vlookup



The results are displayed below:

vlookup


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]

vlookup

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:

vlookup











Copyright © 2006-2016, LQSystems,Inc. All rights reserved.