What's a VLOOKUP ?
A vlookup is a function which
allows a given value to be looked up from within a table, and an
alternative column to be returned from that table.
It can also return the closest match to the value being looked up so that each row in the table can represent ranges of values.
The table being looked into can be within a different spreadsheet to
the one currently being worked on, and this spreadsheet can be shared
between multiple users.
There are many advantages to using a vlookup. The main advantages are
enhanced presentation (data can be looked up and displayed on a form),
and that tables of values don't need to be repeated on the spreadsheet,
significantly reducing space and data maintenance issues.
Once a formula for a lookup has been set up, it can quickly and easily
be copied to different areas in the spreadsheet so that it can refer to
adjacent cells in a list. An example is shown below;
(notd above example shows 3 vlookups - the middle value couldn't be found. Note also the VLOOKUP assistant icon highlighted)
The vlookup function can be entered directly in a cell, by using Insert, Function, or by clicking on the Insert Function icon
Using the Insert Function options
are better option for the novice because they open the "Function
Arguments" window (shown below) which prompt you for each piece of
information required, and offers help.
The arguments that should be specified for the vlookup are;
- The value you're trying to find (lookup_value). This can either
be a literal value, or a cell reference, or a formula. If it is a cell
reference or formula, then the value that will be looked up will be the
value of the cell reference, or the result of the formula.
- A reference to the cells where the results can be found
This is expressed in the form of an Excel range (e.g. $C$1:$D$5), or a
named range of cells. The leftmost column must contain the data being
For example, if we were looking up a branch number, then the full list
of branch numbers should appear in the rightmost column. The data that
should be returned must also exist in the range.
- The relative column number in the Table Array which should be
returned if the value is found. So if we have a table of branch numbers
and branch names (e.g. in the range $C$1:$D$5),
and we want the branch name, we'd specify column 2. Note that this is
relative to the range specified in the formula - not the spreadsheet
(i.e. in the example quoted, we're returning column D, but we specify 2
as the relative column number, not 4).
- An exact match indicator. A 0 in this column (which is the
default) specifies that an exact match must be found. A value of 1 (or
True) can be specified in which case the vlookup will return the last
value which is less than the lookup value.
The range of cells being looked into must be sorted in ascending order
for this to work because the vlookup function will stop looking once it
finds a value higher than the value being looked up, and return the
previous value it saw.
VLOOKUPs can go wrong or be difficult to set up, Add-innovation provides the VLOOKUP assistant to help ;
More on the VLOOKUP