Naming cells

You can make a spreadsheet formula more readable by naming a cell or range of cells. Naming may also be used to make reference to a cell or cells absolute. In other words if you wish part of a formula to remain unchanged, you must make the relevant cell reference absolute. Typically, the software prefixes the row and column reference with a $ sign when you make it absolute. An example would be if you wanted the formula to read =(a3+b3)*a6 in a column of cells where a6 is a constant without absolute reference a different formula would follow in each cell. See below.

As you can see the figures on the right are different although the data input is the same. This is because each cell in the range C3: C7 are multiplied by A6. In the left-hand column as the formula is brought down so the cell references change ending with cell C7 multiplied by A10.

The cell A6 could have been given a text name in the active cell reference bar and this would have the same effect

The naming of a cell or making it absolute is only needed when the contents of that particular cell are needed as a constant for all formula references. It ensures the values of the named cell stay the same no-matter where the formula is placed.

VLOOKUP and HLOOKUP

These functions allow you to search for a matching value in a defined area of your spreadsheet and if a match is found then it will display a corresponding value for that match.

Create a spreadsheet and enter in the following information:

Define a named range for cells A2 to D4 and call it STOCK

Syntax for VLOOKUP

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Lets now look at how this can be applied to the spreadsheet we have just created.

Now move further down your worksheet or to a new worksheet if you wish and enter the formula =VLOOKUP (A7, STOCK, 4,FALSE) in cell B7 and in cell A7 enter the stock item you wish to look up the price for.

The above formula looks at what you have typed in cell A7, then goes to the range named and looks in the 4 th column to find the corresponding price.

If you have entered the word FALSE as the final argument then Excel will look for an exact match. If it cannot find one then #N/A will be displayed.

If the final argument is TRUE (i.e. the list is sorted), or omitted, then Excel will look for the closest match which will be then nearest one alphabetically.

TASK

Once you have entered in the data and the VLOOKUP formula test it for all three items.

Now try to create another VLOOKUP for displaying the stock level when an item is displayed.

REMEMBER : VLOOKUP always uses the first column in any named range to search for a match.

HLOOKUP

This acts in exactly the same way as VLOOKUP except that it searches across (H is for horizontal, V is for Vertical)