Exercise 1
Open the file contained within the folder name shown above.
We want Excel to search through the codes in this sheet:
This spreadsheet will be your lookup table.
When Excel finds the code in column A that exactly matches the code in cell B18, we want
Excel to complete cells B19:B24 with the correct information for that site:
We need to use an exact match lookup formula.
First create a range name for all the data in your main spreadsheet table (A4:G16).
Also create a range name for cell B18 where you will keep changing the site code.
In cell B19 create an exact match lookup formula to return the site location from column B
(the second column in your lookup table).
Remember - exact match lookup formulae have an extra variable:
=VLOOKUP(Cell to lookup, Table range, Column ref, FALSE)
Copy the formula down to cell B24 and then edit each formula by changing the column
reference to return the correct item data.
Change the site code in cell B18 to B92/771 to check that all your formulae work.
Use Save As... to save the file in your own new Excel work folder.
Exercise 2
Create the following range names:
RatesTable - referring to H6:I9
Hours - referring to D6:D16
Rates - referring to E6:E16
Use a Vlookup function in a formula to calculate the total for each item.
It's enough to make you think about a career change
Save the file with the same name and close it down.
Exercise 3
Four range names have been created in this file. Use the name box to find out what they refer
to.
Click a range name to select the cells it refers to
Create a Vlookup function in each of the yellow cells to return the base price and multiplier
for the chosen destination and holiday type.
The hedonistic holiday to Thailand is the most expensive
Try typing in different destinations and holiday types to make sure the functions are working
properly.
Save the file with the same name and close it down.
Exercise 4
Four range names have been created in this file. Use the name box to find out what they refer
to.
Click a range name to select the cells it refers to
Create a Vlookup function in each of the yellow cells to return the base price and multiplier
for the chosen destination and holiday type.
The hedonistic holiday to Thailand is the most expensive
Try typing in different destinations and holiday types to make sure the functions are working
properly.
Save the file with the same name and close it down.
Exercise 5
On the Lookup Table worksheet give the table a sensible range name.
TaxTable might be a sensible name for the table
On the Main List worksheet create Vlookup functions to work out the tax band, 12 months
tax, and 6 months tax for each of the cars.
The function should look up the emissions
Save the file with the same name and close it down.
Exercise 6
Open the file contained within the folder named above.
We want to calculate the bonus for each employee:
An =VLOOKUP formula will reference the table in columns E and F.
In cell C4, create an =VLOOKUP formula to calculate the bonus for each employee.
Use the bonuses and customer numbers table in column E and F as your lookup table.
Creating a range name will help to use in the formula:
=VLOOKUP (Lookup cell, Table Range, Column ref)
Change a few bonuses and customer numbers in columns E and F to check your formula
works.
Use Save As... to save your file in your own new Excel work folder.
Exercise 7
Create the following two range names:
Code - referring to H3
Stock - referring to B4:E12
Create a lookup function in each of the yellow cells to return the details for the product code
typed into the green cell.
Typing in a different code should return the correct data
Save the file with the same name and close it down.
Exercise 8
Open the file contained within the folder named above.
We want to calculate the discount due on each product:
We need to create a lookup table for the =VLOOKUP formula.
In cell E4, create an =VLOOKUP formula to calculate the discount.
Use the discount rates and quantities table in column H and I as your lookup table. Creating
a range name will help to use in the formula:
=VLOOKUP (Lookup cell, Table Range, Column ref)
Change a few of the discount percentages in the lookup table to check your formula works.
Use Save As... to save the file in your own new Excel work folder.
Exercise 9
Open the file contained within the folder name shown above. We want to display the rating
for each curry:
The curries are rated from Very Mild to a worrying Health Warning!.
In cell C4, create an =VLOOKUP formula that displays the rating message for each curry.
Use the chilli rating table in column E and F as your lookup table. If you create a range name
you will be able to use this in your formula:
=VLOOKUP (Lookup cell, Table Range, Column ref)
Change the Very mild rating to 1140 and the highest rating of 20 to 18 to check that your
formula works:
Only one curry carries a health warning!
Use Save As... to save the file in your own new Excel work folder.
Exercise 10
Open the file contained within the folder name shown above. We want to display the rating
for each curry:
The curries are rated from Very Mild to a worrying Health Warning!.
In cell C4, create an =VLOOKUP formula that displays the rating message for each curry.
Use the chilli rating table in column E and F as your lookup table. If you create a range name
you will be able to use this in your formula:
=VLOOKUP (Lookup cell, Table Range, Column ref)
Change the Very mild rating to 1140 and the highest rating of 20 to 18 to check that your
formula works:
Only one curry carries a health warning!
Use Save As... to save the file in your own new Excel work folder.
Exercise 11
Fill in the unit prices table based on the following categories:
1 - 499 = £1
500 - 999 = £0.90
1000 - 1499 = £0.80
1500 or more = £0.70
Your Lookup table should begin as shown in the diagram below:
Start with the smallest amount that could be ordered
Use a Vlookup function to calculate the total value of each order.
Your formula should lookup the quantity ordered
Save the file with the same name and close it down.
Exercise 12
Open the file contained within the folder name shown above.
We want to work out the right size jacket to order for each dog:
The table on the right shows which size fits the different neck sizes.
In cell C4, create an =VLOOKUP formula that chooses a jacket size for each dog. Use the
neck sizes and jacket sizes table in column E and F as your lookup table. Creating a range
name will help to use in the formula:
=VLOOKUP (Lookup cell, Table Range, Column ref)
Change the XL message to Extra Large and the S to Small and the small threshold from 20
to 18 to check that your formula works:
Your answers should be very similar to those here.
Use Save As... to save the file in your own new Excel work folder.
