Excel using

V-lookup
and
H-lookup

IT Training
St. George’s, University of
London
Contents

Understanding Tables................................................................................................................. 1
Creating A Table From Data ........................................................................................................................... 2
Inserting Table Columns ................................................................................................................................ 3
Removing Table Columns............................................................................................................................... 4
Working With Table Styles ............................................................................................................................. 5
Inserting Or Deleting Table Records .............................................................................................................. 6
Removing Duplicates ..................................................................................................................................... 7
Filtering Tables ............................................................................................................................................... 8
Renaming A Table .......................................................................................................................................... 9
Deleting A Table ........................................................................................................................................... 10
Understanding Data Lookup Functions ..................................................................................... 11
Using VLOOKUP............................................................................................................................................ 12
Using VLOOKUP For Exact Matches ............................................................................................................. 13
Using HLOOKUP ........................................................................................................................................... 14
Using MATCH ............................................................................................................................................... 15
Using IFERROR.............................................................................................................................................. 16
Absolute Versus Relative Referencing....................................................................................... 17
Relative Formulas......................................................................................................................................... 18
Problems With Relative Formulas................................................................................................................ 19
Creating Absolute References...................................................................................................................... 20
Understanding Data Validation ................................................................................................ 21
Creating Drop-Down Lists ............................................................................................................................ 22
Understanding Labels And Names ............................................................................................................... 23
Lookup Functions ......................................................................................................................................... 24
Lookup Functions ......................................................................................................................................... 25

If you have a St. George’s username and password you can access all the files that goes with this manual.
Files can be found in a folder on the N drive in the IT Training folder named:
Excel using V-lookup and
H-lookup N:\IT Training\ Excel using V-lookup and H-lookup
UNDERSTANDING TABLES
Most spreadsheets provide three functions: calculations much simpler and accessible; the
calculating, graphing, and some limited database charting tools allow users to create graphs; and
functionality. Excel continues on in this tradition. databases can be constructed using tables.
In Excel, formulas and functions make complex

What Is A Table?
A table is made up of columns and rows and can be used to store many different kinds of information.
In applications such as Microsoft Word, a table can be used to create page layouts and lists as well as
perform many other tasks with the main purpose of formatting information. However in Excel the
primary use for a table is to store data. A table that stores data is sometimes known as a database.
Once the data is stored in a table it can then be processed.

In Microsoft Excel a table appears with quite a specific structure. Database tables are organised into
records (rows) and fields (columns) so that the data can easily be sorted and queried. The first row is
normally reserved for the headings – known as field names.

One of the normal database operations you can complete in Excel using a table is sorting the data. For
instance, you can query the data either through the use of filters, or through more advanced
techniques involving criteria (querying by example).

Database Features On The Ribbon

In Excel, the commands for working with a database can be found on various tabs on the ribbon. If you
want to work mainly with the appearance of a table then you can use the commands on the contextual
TABLE TOOLS: DESIGN tab which appears when the table is active (see below).

If you are looking for ways to work with data then you may want to use the commands in the Editing
group on the HOME tab; for example, the Sort & Filter command which allows you to sort and filter
data in a worksheet as well as in a table. The DATA tab also contains a Sort & Filter group that contains
a number of commands for sorting data and creating filters.

1
 Excel - Lookups

CREATING A TABLE FROM DATA

A table can be created from data that is presented list. Generally, anything you can put in a list
logically and continuously in rows and columns. (names and addresses, inventory items, etc.) can
Data you will be converting into a table should be set up as a table in Excel.
contain column headings and be organised as a

Try This Yourself: 2

Before starting this exercise you

Open
File

MUST open the file E1356

Worksheet Tables_6.xlsx...


Click in any cell that contains
data


Click on the INSERT tab, then
3
click on Table in the Tables
group to display the Create
Table dialog box
Excel calculates the tables
range by extending in all
directions until empty rows or
columns are reached…


Click on [OK] to create the
table


Click in any cell in the table to 5
remove the selection
highlighting


Click on the TABLE TOOLS:
DESIGN tab, then click on the
More arrow for the Quick
Styles gallery in the Table
Styles group to display a
gallery of options


Point to an option to see it
applied to the table in Live 7
Preview


Click on Table Style Medium 4
to apply this style to the table

For Your Reference… Handy to Know…

To create a table in Excel:  By advising Excel that the table has headers,
1. Click anywhere in the list that will become the column headings in the first row become
the table field names and appear with sort and filter
arrows. If the table doesn’t have headings,
2. Click on the INSERT tab, then click on Table
Excel will add its own as Column 1, Column 2
in the Tables group
and so on.
2
 Excel - Lookups

INSERTING TABLE COLUMNS

A table is a defined area in an Excel worksheet. end. However, you can add more fields if required.
When a table is created, its range must be When you choose the appropriate command, Excel
specified so that Excel can determine where the will automatically adjust the size of the table.
fields (columns) and records (rows) begin and

2
Try This Yourself:
Continue using the previous
Same File

file with this exercise, or open

the file E1356 Worksheet
Tables_3.xlsx...

 Click in cell B4

 Click on the HOME tab, then

click on the drop arrow for
Insert in the Cells group 4

 Select Insert Table Columns

to the Left to insert a new
column

 Click in cell B3 (the new

column heading), type
Colour, then press

 Ensure that cell B4 is

selected, type Green, then
press
8
 Click in cell E5
This cell is in the Type
column…

 Repeat step 2 to select Insert

Table Columns to the Right
to insert a new column

 Click in cell F3 (the new

column heading), type Year,
then press

For Your Reference… Handy to Know…

 If you have duplicate headings in a table,
To add a new column to a table:
Excel automatically numbers them
1. Click in the location for the new column
sequentially from left to right. For instance, if
2. Click on the HOME tab, then click on the drop
you have two headings titled Age, the one
arrow for Insert in the Cells group
closest to the left edge of the table will
3. Select Insert Table Columns to the Left, or
remain Age, while the one closest to the
Insert Table Columns to the Right
right will become Age2.

3
 Excel - Lookups

REMOVING TABLE COLUMNS

In a proper database application, removing a the data. However, it is relatively easy to remove
column (field) from a table can be a complex existing columns from a table in Excel. You can
process. This is mostly to protect the integrity of simply use the options provided in the cells group
on the HOME tab.

2
Try This Yourself:
Continue using the
previous file with this
Same File

exercise, or open the

file E1356 Worksheet
Tables_4.xlsx...

 Click in cell B4
This is the Colour 3
column of the table…

 Click on the HOME tab,

then click on the drop
arrow for Delete in the
Cells group

 Select Delete Table

Columns to delete the
selected columns
Only one column will be 5
deleted.
Notice that the column
and its data have now
been removed…

 Click in cell E4

 Repeat step 2, then

select Delete Table
Columns to delete the
Year column

For Your Reference… Handy to Know…

To remove a column from a table:
1. Click in the column you want to remove  You can delete a column using either the
2. Click on the HOME tab, then click on the Delete Table Columns command (preferred)
bottom half of Delete in the Cells group or the Delete Sheet Columns command
3. Select Delete Table Columns which deletes the entire worksheet column.
If you make a mistake and delete by
accident simply click on Undo in the QAT.
4
 Excel - Lookups

WORKING WITH TABLE STYLES

Choosing the appropriate table style can understand. The available style options change not
improve the appearance of the data contained in only the appearance of the table but also some of
the table and therefore make the data easier to the features available within it.

3
Try This Yourself:
Continue using the previous file
Same File

with this exercise, or open the

file E1356 Worksheet
Tables_2.xlsx...

 Click in cell A4 to make the

table active

 Click on the TABLE TOOLS:

DESIGN tab, then spend a few
moments studying the options 5
in the Table Style Options
group

 Click on Header Row in the

Table Style Options group so
that it appears unticked
When the Header Row is
unticked the header row does
not appear in the table…

 Experiment with the other

options in the Table Style
Options group to see how both
the format and structure of the
table can be changed

 When you have finished

experimenting, ensure that the
settings in the Table Style
Options group are selected as
shown

For Your Reference… Handy to Know…

To change styles in a table:  The Total Row option on the TABLE TOOLS:
1. Click on the table DESIGN tab inserts a =SUBTOTAL function
using a COUNTA setting to count the
2. Click on the TABLE TOOLS: DESIGN tab
number of records. You can modify this
3. Click on the various Table Style Options to
formula simply by clicking on it as you would
enable or disable them
with any other formula in a worksheet.
5
 Excel - Lookups

INSERTING OR DELETING TABLE RECORDS

In a database table, each row is known as a worksheet, it is critical that Excel knows the extent
record. Obviously, removing or adding records in of the table at all times. There are specific
a table will determine the overall size of the commands in Excel that should be used for
table. Since a table in Excel is simply a range in a inserting and deleting rows (records) in a table.

Try This Yourself:

Continue using the previous

Same File

file with this exercise, or

open the file E1356
Worksheet Tables_7.xlsx...


Click anywhere in row 7 in the
table
2

Click on the HOME tab, then
click on the bottom half of
Insert in the Cells group


Select Insert Table Rows
Above to insert a new row at
row 7


Select the range A10:I10,
then copy and paste it into
the range A7:I7
This will effectively duplicate 3
this record – we’ll deal with
this a little later…


Click in the last cell in the
Telephone column (this will
be cell I94)


Press
Notice that a new row is
inserted into the table…
4

Ensure that the new row is
active, click on the HOME
tab, click on the drop arrow
for Delete in the Cells group,
then select on Delete Table
Rows

For Your Reference… Handy to Know…

To insert or delete rows within a table:
 You can insert or delete sheet rows and/or
1. Click in the row where you want to insert or
table rows. The difference is that if you
delete table records
choose the table option, the row is only
2. Click on the HOME tab inserted or deleted within the table area and
3. In the Cells group, click on the bottom half of not across the entire worksheet.
Insert or Delete, then select the required option
 You can also insert or delete table columns
6
 Excel - Lookups

REMOVING DUPLICATES
Duplicate entries are an issue when using Excel columns, you don’t have the same checks and
as a database. Most database systems have balances. Fortunately, there is a command
primary keys to identify unique records. Because available that will check for duplication in a table.
tables in Excel are simply normal rows and

Try This Yourself: 2

Continue using the previous file
Same File

with this exercise, or open the

file E1356 Worksheet
Tables_8.xlsx...

 Click in a cell within the table to

make it active
If you look closely you’ll see that
row 7 is duplicated at row 10…

 Click on the TABLE TOOLS:

DESIGN tab, then click on
3

Remove Duplicates in the Tools

group to display the Remove
Duplicates dialog box

 Click on [Unselect All] to

remove the ticks from all of the
columns
This table has a unique
identifier (the employee 5
number) which can be used to
check for duplicates…

 Under Columns, click on No so

it appears ticked

 Click on [OK]
A message will appear advising
6

the number of duplicates that

have been removed…

 Click on [OK]
Notice that the duplicate at row
10 has now gone

For Your Reference… Handy to Know…

To remove duplicates from a table:
1. Click in the table  If you are looking for a duplicated record
and don’t have a unique field (such as an
2. Click on the TABLE TOOLS: DESIGN tab, then
click on Remove Duplicates in the Tools group employee number) in your table, select all of
the columns. If two records have the same
3. Specify the columns to check for duplicates,
then click on [OK] details in each column then they must be
duplicated.

7
 Excel - Lookups

FILTERING TABLES
Filtering, which allows you to display specific in a filtered list. You can easily specify more
records that match a certain criteria provides advanced ways of filtering and can turn the filter
you with a great opportunity for displaying and results on or off with the click of a button.
hiding records. You can display up to 1,000 items

Try This Yourself:

Continue using the

previous file with this
Same File

exercise, or open the file

E1356 Worksheet
Tables_10.xlsx...


Click on the filter drop
arrow for the Age
column, as shown


Click on Select All to
remove all of the ticks,
then scroll down and
click on 65
1
This specifies that only
employees aged 65 will
be displayed…


Click on [OK] to display
the employees aged 65


Click on the filter drop
arrow for the Office 3
column, click on Select
All to remove all of the
ticks, click on New York,
then click on [OK]

This will show all 65 year

old employees in New
York…

4

Click on the DATA tab,
then click on Clear in the
Sort & Filter group to
For Your clear all of the filters and
Reference… Handy to Know…
see all of the records
To filter data
againin a table:  When you create a filter in a table, Excel
1. Click anywhere in the table area simply hides the rows that don’t match the
2. Use the filter arrows to select the data you filter. That’s why you’ll see some row
wish to view numbers missing.

3. Click on [Clear] to remove the filter

8
 Excel - Lookups

RENAMING A TABLE
In Excel you can have more than one table in a named Table1, Table2 etc. by default; however,
worksheet or workbook. Since a table is actually you can assign more meaningful names if you
part of a range in a worksheet, each table is prefer.
assigned its own unique range name. Tables are

1
Try This Yourself:
Continue using the previous
Same File

file with this exercise, or

open the file E1356
Worksheet Tables_11.xlsx...

 Click anywhere in the

table to make it active,
2

then click on the TABLE

TOOLS:DESIGN tab
Notice that the name of
the table (Table1) appears
in Table Name setting in
the Properties group…
3
 Click on Table1 in Table
Name to select it

 Type Employee Table,

then press
Spaces are not allowed in
range names…


5
Click on [OK]

 Click on Table1 which has

reappeared in Table
Name, type Employees,
then press
The table will be renamed

For Your Reference… Handy to Know…

To rename a table:  Table names are range names and are

1. Click in the table, then click on the TABLE therefore subject to the same naming
TOOLS: DESIGN tab conventions. While spaces are not permitted
in range names, an underscore may be used
2. Click on the existing table name, type the
instead (e.g. Employee_Table).
new name, then press

9
 Excel - Lookups

DELETING A TABLE
There may come a time when a table is no the table the table itself will remain in place but
longer required. Deleting a table is quite simple, some of its data, headings, or structure may
providing the entire defined range of the table is change depending upon the cell or range you have
selected first. If you attempt to delete parts of deleted.

Try This Yourself:

Continue using the previous
Same File

file with this exercise, or open

the file E1356 Worksheet
Tables_13.xlsx...

 Click in cell C3, then press

The text of the heading is
1

deleted but immediately

replaced by the text Column1…

 Select the range C3:D9, then

press
The data will be deleted….

 Select the range A3:I21, then

press
2

The entire table, including its

formatting will be removed…

 Select the range A23:I95, then

press to delete this table

 Click on the FORMULAS tab,

then click on Name Manager
3
in the Defined Names group
to display the Name Manager
dialog box
Since there are no names
listed here we can safely
assume that the table has
been deleted…

 Click on [Close] 5

For Your Reference… Handy to Know…

To delete a table from a worksheet:  The Name Manager dialog box is a useful
1. Select the entire range of the table way of finding out what tables, if any, you
have in a worksheet. If you click on the
2. Press
[Filter] button you can filter the names to
show only table names.

10
 Excel - Lookups

UNDERSTANDING DATA LOOKUP FUNCTIONS

Data lookup functions are used to retrieve data often part of a table of information which can be
from a table. They generally require at least two referred to as a calculation area. The where to
pieces of information; what to look for and look for it is known as a data table – a table in
where to look for it. The what to look for part is which a list of rates, figures, text or other items are
held.

1
Data Area
The data area is often on a worksheet by itself, protecting it from accidentally being modified or
deleted. It holds all of the possible values for the data. The values are laid out in a table format and
they are listed in numerical or alphabetical order of the code that the lookup function will search
for.

The name Pay-Rates has

been created as a quick
way to reference the
data table in the range
B3:C7…

Calculation Area
The calculation area is usually on a worksheet by itself unless you require the data values to be
2
visible as well as the resulting calculations.

The calculation area uses a formula, such as VLOOKUP, to find the correct data for each situation.
In this example, the VLOOKUP function shown is comparing the value in C5 with the values in the
range Pay_Rates. It then returns the value in the second column of the data table, determined by

The formula here takes the Pay Scale

value in C5 (i.e. 2) and finds the
corresponding row in the Pay_Rates
table (i.e. B4). It then returns the value
in the second column of the
corresponding row of the Pay-Rates
table, which in this case is $30.00…

11
 Excel - Lookups

USING VLOOKUP
The classic lookup function is VLOOKUP – the It then looks across the table to the column you
vertical lookup. This function searches vertically have specified to find the value to return. This is
down a sorted data table looking for a match ideal for looking up numeric values within a range,
with the lookup-value (or the next lowest value). such as tax rates, or commission amounts.

Try This Yourself: 1

Continue using the previous file with
Same File

this exercise, or open the file E831

Lookup Functions_2.xlsx...

 Click on the drop arrow for the

Name Box and select Tax_Table
This is the data table for our
VLOOKUP function…

 Click on the Payroll worksheet tab to

return to the payroll table and click
on G5 4

 Type =VLOOKUP(F5,Tax_Table,2)

 Press
0% appears because the Gross Pay is
less than $500…

 Copy the formula in G5 to the range

G6:G13

 Enter the formula =F5*G5 in H5,

then copy it to H6:H13

 Enter the formula =F5-H5 in I5, then

copy it to I6:I13

 Click on C10

 Type 2 and press

The change in the Pay Scale results in
changes to the Hourly Rate, Gross
Pay, Tax Rate, Tax and Net Pay for
9
Alana Keane

For Your Reference… Handy to Know…

VLOOKUP(lookup_value,table,col_index_num)
This function searches down the left-most column of the An alternative to the VLOOKUP function is the
table until it finds the lookup-value or the row with the horizontal or HLOOKUP function. This looks for
next lowest value. It then refers to the column index a value in the top row of a table or array of
number nominated in the function, and returns the values, and returns the value from the same
value found in the corresponding row and column. column in the row you specify.

12
 Excel - Lookups

USING VLOOKUP FOR EXACT MATCHES

Generally, the VLOOKUP function uses three may only want to return a value if you get an exact
arguments: the lookup-value, the table location match, and the optional fourth argument, match-
and the column number. This lookup will find a type, makes this possible. By adding FALSE to the
row based on a numeric range. However, you function, #NA is returned if a match isn’t found.

Try This Yourself:

Same File

Continue using the previous file with

this exercise, or open the file E831
Lookup Functions_3.xlsx...

 Click on the drop arrow for Name

Box and click on Items_List
This lookup table includes specific 3
product codes that require an exact
match…


Click on the Invoice worksheet tab,
then click on C7
We’ll use the exact lookup to find
the description…

 Type =VLOOKUP
(B7,Items_List,2,FALSE)

 Click on E7, type

=VLOOKUP(B7,Items_List,3,FALSE),
6

then press
We only want to charge a deposit if
B3 is not blank…

 Click on F7, type

=IF(ISBLANK($B$3),0,
VLOOKUP(B7,Items_List,4, FALSE)),
then press

 Click on G7, type =(D7*E7)*(1-F7),

then press

 Copy the formulas in columns C, E, F

and G down to Row 10 to complete
7
the invoice as shown

For Your Reference… Handy to Know…

To use VLOOKUP for exact matching:  You can use the ISNA(value) function to trap
VLOOKUP(lookup_value,table,col,range_lkup) #NA results. For example, the structure
Use the value of FALSE for range_lkup when you =IF(ISNA(lookup),”Code not found”,lookup)
want to ensure exact matches only. #NA will be will display a useful message when an exact
returned if an exact match isn’t found. match is not possible, rather than #NA
which does not explain the problem clearly.

13
 Excel - Lookups

USING HLOOKUP
Another commonly used lookup function is the next lowest value). It then looks down the
HLOOKUP – the horizontal lookup. This function table to the row you have specified to find the
searches horizontally across a sorted data table value to return. This can be used to search for text,
looking for a match with the lookup-value (or numbers, or logical values.

Try This Yourself:

Continue using the previous
Same File

file with this exercise, or open

the file E831 Lookup
Functions_4.xlsx...

 Click on the Index worksheet

tab then click on D14 and
examine the formula
This is an example of the
VLOOKUP function. This scans
down the list of options
looking for the corresponding
1
Equipment number. When it
finds a match, it returns the
description.
You can use HLOOKUP in a
similar way…

 Click on cell D15 and type

=HLOOKUP(C15,Rate_Type,2,
TRUE)

 Press
The name of the Rate Type will
be returned and displayed…

 Experiment with different

values in cells C14 and C15 3
You’ll find that the formulas
return the corresponding
descriptions

For Your Reference… Handy to Know…

 If the lookup_value is smaller than the
HLOOKUP(lookup_value,table,row_index_num)
smallest value in the first row of table,
This function searches across the top-most column
HLOOKUP will return the #N/A error value.
of the table until it finds the lookup_value or the
row with the next lowest value. It then refers to  Range_lookup is an optional argument. If
the row index number nominated in the function, TRUE or omitted, it will find the closest match
and returns the value found in the same column in the top row. FALSE forces an exact match.
and corresponding row.
14
 Excel - Lookups

USING MATCH
The MATCH function looks for an item in a list itself is needed. For example, you can search for a
and returns the position of that item. MATCH is particular category name in an alphabetical list and
used instead of the other lookup functions when return its location in the list, such as 3 for the 3rd
the position of the item rather than the item position or 4 for the 4th position.

Try This Yourself:

Continue using the previous file
Same File

with this exercise, or open the file

E831 Lookup Functions_6.xlsx... 3

 On the Index worksheet, click on

C18 and examine the formula
At the moment, the formula uses
the Rate Type in C15 to determine
which column to look in. Using
MATCH you can allow the user to
type the rate name rather than a
number...

 Click on cell C15 and type Staff,

then press and delete the
contents of D15 which will display
an error 4

 Click on C18, then double-click on

C15 in the formula to select it –
we’ll replace it with the MATCH
function

 Type MATCH(C15,C4:F4)


Press
The formula looks up the text and
returns its position, which is 4 in
this case. This is, in turn, used by
the INDEX function to locate the
correct hire rate for the given rate
type

For Your Reference… Handy to Know…

MATCH(lookup_value,table,match_type)  When MATCH searches for a text value, it
This function searches through the table until it expects to find the list in alphabetical order.
finds the lookup_value or the row with the next If it doesn’t find an exact match in the
lowest value. It then returns the position of the alphabetical search, it will return the
value in the array. Match_type can be 1 (find value position of the word that would have
less than or equal to lookup_value), 0 (exact appeared alphabetically before it in the list.
match) or -1 (value greater than or equal to). This can be varied by changing match-type.
15
 Excel - Lookups

USING IFERROR
IFERROR is used to trap errors that may occur as return the message #DIV/0! which can be a bit
the result of a calculation and then display alarming for novice users. IFERROR tests a
alternative text or values. For example, if you calculation to see if it works and, if so, performs
divide a number by zero, Excel will normally the calculation. If not, it displays an alternative.

2
Try This Yourself:
Continue using the previous
Same File

file with this exercise, or open

the file E714 Logical
Functions_4.xlsx...

 Click on the IFERROR Function

worksheet tab and click in E7

 Type the following

=IFERROR(C7/D7, "First
Year")

 Press
4

 Click on E7 and then double-

click on the fill handle to copy
the formula down the column
Instead of giving an error
where the divisor is zero, Excel
displays the text “First Year” in
the cell

For Your Reference… Handy to Know…

IFERROR(calculation, error_value)  In this example we’ve used text as the entry

This function performs the calculation and if there to be displayed if an error is located, but you
are no errors, displays the result of the calculation. could just as easily display nothing (using "")
If an error does occur, it displays the error_value. or perform an alternative calculation.

16
 Excel - Lookups

ABSOLUTE VERSUS RELATIVE REFERENCING

Excel is a calculation tool and as such is geared entered using cell referencing a shape is created and it
around the concept of formulas. Formulas are is this shape which determines where Excel goes to
entered into a worksheet using cell references pick up values for use in the calculation.
rather than actual values. Each time a formula is

Understanding Formula Shapes

When you create a formula you don’t enter numbers into the formula – you enter cell references. This
creates a great time advantage when you need to duplicate that formula in other cells. Formulas are
merely shapes that reference cells in specific locations in the worksheet or workbook. For example, let’s
say you have a formula in cell E5 that is written as =D5*C5. To Excel this is interpreted as a shape that
says “from my current position (E5) go left one cell (D5), take the value there and multiply it by the value
two cells left (C5)”. This becomes the formula shape – left one multiply by left two.

When this formula, or shape, is then copied or filled to adjacent cells it is the shape that is copied and
the cell references within those copied formulas change relative to the shape (which doesn’t change).
For example if you copy =D5*C5 from E5 to E6 the formula in E6 changes to =D6*C6 – but the shape is
still – left one multiply by left two.

This is known as relative referencing – because the cell addresses change relative to the current location.

Absolute Referencing
There will be times however when one or more cell references in a formula are always required to be
located in a specific cell, row, or column. In the example below, sales tax of 10% is located in cell B4 and
must always be referenced from B4. The sales tax for the dining table is calculated using the formula
=B8*B4 while the sales tax for the sofa is calculated using the formula =B9*B4. These are different shapes
and if you were to copy the formula from C8 (=B8*B4) to C9 it would copy the shape and the formula
would be =B9*B5 – which is wrong.

When dealing with cells in formulas that must be referenced from the same location you must fix these
cell references by making them absolute. This is done by placing a $ sign in front of either or both the
row and column co-ordinate of the cell depending on which of these has to remain fixed (e.g. $B$4, $B4,
or B$4). In the example above, both row and column co-ordinates need to be fixed and the formula in
C8 should be written as =B8*$B$4 before it can be copied to adjacent cells.

17
 Excel - Lookups

RELATIVE FORMULAS
Most of the formulas that you enter into a be able to change when the formulas are copied to
worksheet, unless you are dealing with quite other locations. This is particularly true of columnar
complex mathematical modelling, will be worksheets where there is a series of columns that
relative formulas where the cell references will all have the same shape and structure.

Try This Yourself: 1

Before starting this exercise you
Open File

MUST open the file E711

Absolute_1.xlsx...

 Click in cell B9 to make it the

active cell

 Click on the Sum command

the Editing group on the Home
in
2
tab of the Ribbon to commence a
formula in this cell

 Press
formula
to complete the

 Click on cell B9 again, and drag

the small square handle (the fill
handle) at the bottom right
4
corner of the cell to E9
This will copy the formula shape
from B9 to E9…

 Click in cell F6 to make it the

active cell

 Click on the Sum command in

the Editing group to commence a
formula in this cell 6

 Press
formula
to complete the

 Click on cell F6 again, and drag

the fill handle to F9
This will copy the formula shape
from F6 to F9

For Your Reference… Handy to Know…

To create a relative formula:  The formulas that we created above copied

1. Click in the cell in which you want the successfully and correctly because the same
formula formula shape is used in each of the cells in
2. Type or enter the formula and ensure that which the formula was copied – this is the
the formula contains references to other real benefit of relative formulas.
cells

18
 Excel - Lookups

PROBLEMS WITH RELATIVE FORMULAS

Copying formulas can present problems when column – that is, the formula must include an
the shape of the formula varies from cell to cell. absolute cell reference. In this exercise you will
This can occur when one or more values in a see some strange values when you copy a formula
formula must be located in a specific cell, row, or to other cells using the default copying method.

Try This Yourself: 2

Before starting this exercise

Open File

you MUST open the file

E711 Absolute_2.xlsx...

 Click in cell C11 where we

need to calculate the
3
product’s surcharge

 Type =B11*B5
Notice the shape of the
formula as indicated by the
highlighted cells…

 Press
formula
to complete the
5

Okay, we can now copy (or

perhaps fill, seeing asthe
destination cells are
adjacent) to the other
cells…

 Click on cell C11 again

 Move the mouse pointer to

the fill handle at the lower
right corner of the cell and
drag down to cell C22
The results are
meaningless!

Handy to Know…

 So what has gone wrong with the formulas above? The whole problem hinges on the fact that each of
the formulas in column C uses a different shape from the other formulas. For example, the first
formula in C11 is =B11*B5. The shape of this formula is one left, then six up and one left.

 The second formula in C12 has become =B12*B6 because it uses the same shape as the originating
formula in C11. In reality the formula should be =B12*B5. All of the formulas above must reference
the Head Office Surcharge of 12% which is in cell B5 – that is, the cell reference must be absolute.
19
 Excel - Lookups

CREATING ABSOLUTE REFERENCES

There will be times when you need to create a references absolute. A cell reference in a formula is
formula which references cells that must remain made absolute by preceding its row coordinate or its
the same even when it is copied around the column coordinate or both with a $ sign.
worksheet. That is, you will need to make the cell

1
Try This Yourself:
Continue using the previous
Same File

file with this exercise, or open

the file E711 Absolute_3.xlsx...

 Click on cell C11 then click on

the B5 reference of the
formula in the Formula bar at
the top of the screen
2
 Press the Absolute key
several times and notice how
the reference cycles through
various $ sign configurations.
Press the Absolute key
until B5 appears as $B$5

 Press
editing
to complete the

 Click on C11, move the mouse

pointer to the fill handle at
4

the bottom right of the cell,

and drag down to C22 to fill
the formulas down –
successfully this time

For Your Reference… Handy to Know…

To make a cell reference absolute:  To make a row reference absolute, press
1. Press to insert $ before the row and twice to insert $ before the row
column references, or type $ before each reference only. To make a column reference
To remove absolute references: absolute, press three times to insert $
1. Press until all $ are removed, or delete before the column reference only.
the $ from the formula

20
 Excel - Lookups

UNDERSTANDING DATA VALIDATION

Data validation is used to define restrictions on users to enter invalid data but warn them when
what data can or can’t be entered in a cell. You they try to type it in the cell. You can also provide
can set validation to prevent users from entering messages to say what input you expect for the cell,
data that is not valid. If you prefer, you can allow and instructions to help users correct any errors.

Access the Data tab. Click on the drop-

down arrow on the Data Validation
button to select from the available menu
list. Alternatively, click on the top section
of the Data Validation button to instantly
access the Data Validation dialog box.

Data Validation Settings

Data validation is invaluable when you want to share a
workbook with others in your organisation, and you
want the data entered in the workbook to be accurate
and consistent. You can enter settings to restrict the
type of information that a user can enter. Here are
some examples:

 Restrict entry to predefined items in a List.

 Restrict numbers outside of a specified range.

 Restrict dates outside a certain time frame (date

or time). On the Settings tab, use the Allow option to control what
type of restriction you wish to set. In the above example,
 Limit the number of text characters entered into a
the Allow option has been set to Whole number and the
cell.
range of numbers set between 100 and 199.
 Validate data based on formulas or values in other
cells.
Input Messages
In addition to the validation settings, you can also choose
to show an input message when the user selects the cell.
This type of message appears near the cell. You can move
this message, if you want to, and it remains until you
move to another cell or press .
Error Alerts
You may want to provide a meaningful error message if a
user types in the wrong information. Using an error alert,
you can choose a warning, stop or information error
message to appear. Used with an input message, the
error alert gives the user instructions as to what they have
done wrong and what information should be entered
instead.
Input messages and error alerts appear only when data is
typed directly into the cells, not when the data is copied
or filled into the cells.

21
 Excel - Lookups

CREATING DROP-DOWN LISTS

If you have a limited number of possible options much easier for the user to complete their data
for a cell, you can create a drop-down list for the entry tasks. Lists are created using the Data
user to select from. This ensures that the Validation settings and a separate list of items is
spelling of the choices is consistent, and makes it stored in the workbook.

Try This Yourself: 1

Continue using the previous file
Same File

with this exercise, or open the file

E765 Validation_5.xlsx...

 Click on the Sources worksheet tab

and read through the lists
6
We’ll use the Titles list as the
entries for the drop-down list…

 Click on the Payroll tab, then click

on cell B10

 On the Data tab, click on the Data

Validation button and then click
the Settings tab

 Click on the drop arrow

Allow and click on List
for

 Click in Source and press

display a list of range names
to

 Click on Titles to select it, then

click on [OK]
8

 Click on [OK] to apply the settings

Because cell B10 is already
selected, a drop arrow will
appear…

 Click on the drop arrow

to display a list of titles
for B10

 Click on Mr to select it and

enter the text in the cell

For Your Reference… Handy to Know…

To create a drop-down list:
1. Type a list of items in a worksheet, then click  The list we used has been given a range
where you want the list to appear name (Titles) to make it easier to locate. It
2. On the Data tab, click on the Data has also been placed on a separate
Validation to and then click the Settings tab worksheet to protect it. To provide even
3. Select List in Allow, type the list range for better protection, you could hide the
Source, then click on [OK] worksheet by right clicking on the worksheet
tab and selecting Hide.
22
 Excel - Lookups

UNDERSTANDING LABELS AND NAMES

Labels are used to refer to individual cells or For example, in a formula that calculates profit, the
ranges of cells as an alternative to using cell profit column’s formula may look like =Income-
references. Names are more descriptive than Expenses, which is more readable than =E12-E9. Here
labels, but they serve the same purpose. are some other examples.
Labels
The term label usually refers to text that you have typed in a cell. If the text appears next to a continuous list of
values, Excel sees the label as a ‘tag’ that represents that range of values. The label can then be used in formulas
instead of direct cell references. If you have used numbers as ‘labels’, such as the year 2004, Excel allows you to
define these as labels too. Note that labels can be used only within the same worksheet.

Here the label Wages, typed

originally in A5, is being used
in the formula in F5 to refer
to the range B5:E5.

Similarly, the label Qtr_1

could be used to refer to the
cells in the range B5:B8.

Names
If you want to create a tag that refers to a range of cells holding values and text, or that you can use in formulas
on other worksheets, you need to create a Name.

Names are like labels except that the name has to be specified against a particular range, and does not usually
appear on the spreadsheet. Names can be used to refer to cells in other worksheets, in other workbooks, and can
even be used to represent a fixed value rather than a range of cells. For example, if you need to use a constant
value in your calculations, but don’t want the value to appear in the worksheet in case it is accidentally changed,
you can define a name and assign it a value. For example, GST could represent the value 10%.

The name ExpenseTotals

is being created in the
Name box to refer to the
non-contiguous range
A5:A8 and F5:F8.

This name can be used to

re-select the range later,
or to refer to this range
from another part of the

Need to Know…
There are two important rules to follow when creating Names:
1. You can only use letters of the alphabet, numbers or the underscore character ( _ ) in names.
Spaces and other special characters such as &, * or % are not allowed.
2. Names must not begin with a number. Use a letter or an underscore as the first character of your
name, for example, Yr1996 or _2005.

23
 Excel - Lookups

LOOKUP FUNCTIONS
Practice Exercise

Tasks:

Before starting this exercise you MUST have completed all of the
topics in the chapter Lookup Functions…

 Open the workbook called PE_Lookup Functions.xlsx (you will find it

in the student files folder)

 Study the data on both worksheets

 Use the LOOKUP function to reference the Named Ranges worksheet

and to add the Staff Name and Department to cells C6:C8 and D6:D8
respectively on the Awards worksheet

 Use the INDEX functions in cells E6:E8 to reference the winnings table
of the Named Ranges worksheet, based on the following information
on the Awards worksheet:
Quarter: see cell B3
Place: see cells A6:A8 for first, second and third

 Copy and modify the formulas so that they refer to the correct cells
for each quarter, completing the worksheet as shown in the sample
on the next page

 Use the Save As command to save the worksheet as PE_Lookup

Functions (Completed).xlsx

24
 Excel - Lookups

LOOKUP FUNCTIONS
Practice Exercise

25