563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
This exercise tests your knowledge of the MATCH and INDEX functions. First open the workbook in the folder above.
Someone has thoughtfully created two new input cells, allowing you to choose which model year and which Calculations sheet line item you want to report:
You can choose any year and line item you want!
The first thing you should do is to choose values for these two cells on the Inputs sheet.
Your task is to create formulae to fill in the yellow cells on the Calculations sheet:
In this instance we're looking up the daily expenses for the third year, so get (£500).
It may help you to know that two range names have already been created for you. In the diagram below, the blue range is called LineItems, and the orange one NetData.
Two range names have been created for you.
Create formulae as follows:
Use a MATCH function to show the year index number (within its range) for the year you have chosen in cell E12 (if all goes well this should return a number between 1 and 10, depending on which model year you chose to report on)
Use another MATCH function to show the line item index number (within its range) for the line item you have chosen in cell E13 (if all goes well this should give a number between 1 and 3, depending on which line item you chose to report on)
Use INDEX to show the value for this year and line item in cell E14
If you have time, try showing off by doing the whole thing in a single formula in cell E17.
Save this workbook as With values, then close it down.
You can find other training resources for the subject of this exercise here:
25 Aytoun Street