Add-Innovation Home

About Add-Innovation

Contact Us

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;

Example of a vlookup in excel

(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

function button on toolbar

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.

Excel's function wizard form

The arguments that should be specified for the vlookup are;

  1. 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.
  2. A reference to the cells where the results can be found (Table_Array). 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 looked up. 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.
  3. 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).
  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 Assistant