VLookup Worksheet Function

by Linda Quinn

Close Window



VLOOKUP finds a specific row in 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.

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

vlookup



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)


vlookup



The results are displayed below:

vlookup


Here's what happened:



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


=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================
Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.


====================================================================
Want an expert to help with your project?    LQ Systems, Inc.   Business Solutions
====================================================================