This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel (113 exercises)
Topic ==>	Advanced lookup functions (4 exercises)
Level ==>	Harder than average
Subject ==>	Excel training

Create MATCH and INDEX functions to look up the value of a cell given its row and column label

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.

Before you can do this exercise, you'll need to download and unzip this file .

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)