Functions (continued)

We continue our look at functions with lookup and reference functions.

Lookup & Reference Functions

The final group of Excel functions dealt with in this lecture series are called Lookup &
Reference. These let you search for a value in a specified range - if you have a name in one
area, with dates of birth in another, you could use a function to match up names with dates.
These functions aren't always as easy to use, so it helps to make use of the Formula Palette.

 MATCH finds the row or column in which a value is stored in a one dimensional array
 INDEX returns the value specified by the row plus column reference
 HLOOKUP and VLOOKUP combine the above but in a fairly complicated way

1. Open the exceldata.xlsx file, found in Canvas.

2. Click on the Sheet1 tab.
3. In cell A1 type Brass then press [Enter].
4. In A2 type =match( then click on the [Insert Function] button.
5. Set the Lookup_value to A1 - press [Tab].
6. For the Lookup_array, click on the students tab (to move to that worksheet) then on the
letter B at the top of the second column - press [Tab].
7. Set the Match_type to 0 and press [Enter] (0 gives you an exact match).

The result tells you what row number contains the search value.

8. Move to cell A3 and type =INDEX(data,a2,10) and press [Ctrl-Enter].

9. The number needs to be converted to a date so use the pull down in the Number area
on the Home tab and choose Short Date - you have the date of birth of the student
from the search value.

The example needs a little further explanation. The word data in the INDEX function refers to a
named data range (the video for splitting windows demonstrates named ranges). This has
already been set up for you and refers to all the information on the students' sheet. Dates of
birth are stored in column J - the 10th one in the data area.

10. Move to cell A1 and type Cox then press [Enter]. The cell values change to match the
values for the change you made.
HLOOKUP and VLOOKUP work by returning the value in a specified column or row which
corresponds to certain value in another row or column within a two dimensional data area.
Essentially they combine MATCH and INDEX.

There are restrictions in how they work, however:

 The lookup data must be in the left column (or top row) of the array
 The data must also be sorted alphabetically, A to Z

11. Move to cell B1 and type =VLOOKUP( then click on the [Insert Function] button.
12. For the Lookup_value type A1 then press [Tab].
13. For the Table_array, click on the students tab then type b:j (i.e. students!b:j) – since
VLOOKUP works on the left column, you need to exclude column A.
14. Press [Tab], then for the Col_index_num, type 9 and press [Enter] or click [OK].
Note that you do not have to complete all of the requested fields. The Range_Lookup is
optional.
15. Move to A3, click on the [Format Painter] then click on B1 to turn the number into a
date.

The function worked because both of the conditions mentioned above were met.
We used VLOOKUP because the data is in columns. If the data was patterned in rows, you’d
use the HLOOKUP function – it works exactly the same way, but using rows rather than
columns.

To see what happens if the conditions are not met, let’s try looking up the date of birth given a
student's user id.

16. In C1, type =INDEX(data,A2,7) - press [Enter].

17. In C2, type =VLOOKUP(C1,students!G:J,4) - press [Enter].
18. Move to A3, click on the [Format Painter] then click on C2 to turn the number into a
date.
The result doesn’t match the other dates in your worksheet, because the user id information is
not sorted.

If you resort the data by user id, then the VLOOKUP function in cell B1 would change and be
incorrect because that formula is dependent on the data being sorted by column B.

Great care must be taken when using LOOKUP functions. They are fine where data is fixed
(and, better still, protected). Where the data might be sorted on a different column or row, the
functions can be extremely volatile and should not be used.

To see how to use Lookup & Reference functions nested together, watch the lecture
video.
User-Defined Functions
You can create your own functions in Microsoft Excel. Doing so involves using the Visual Basic
Editor. At first sight, this looks very complicated but you can also use it simply, without knowing
anything about computer programming.
This next exercise creates a function to generate random numbers which don't change.
First, you need to display an extra tab on the Ribbon:
19. Move to the File tab then choose Options.
20. Click on Customize Ribbon and click the box next to Developer in Main Tabs on the
right– press [Enter] or click [OK].
21. Click on the new Developer tab and click on the [Visual Basic] button on the far left.
22. Now open the Insert menu and choose Module.
23. Type function myround(x) then press [Enter] - the Editor automatically adds a line
reading End Function. This defines the function with its name and its arguments.
24. Now type in the required calculation, using the function name you already defined in
the last step on the left-hand side to store the result - type: myround = rnd() * x
25. Close the Editor by clicking on the top right red [Close] button - your function is
automatically saved
26. In cell A6 type =myround(100) and press [Enter] (you should get a random number
between 0 and 99.99)

Unlike the example we did previously, pressing F9 does not change the number.
The reason the number doesn't change is because you are using the Visual Basic function
RND() and not the Excel function RAND(). This particular function always gives you the same
sequence of numbers, but with a random distribution.
To get genuinely random numbers, let’s try another module.

27. Click on the Developer tab and click on the [Visual Basic] button on the far left.
28. Click below the existing function and type function myrnd(x) then press [Enter] -
the Editor automatically adds a line reading End Function.
29. Type in the required calculation myrnd = rnd() * x
30. Press enter and add a new second line immediately after function myrnd(x) which just
says randomize
31. Close the Editor by clicking on the top right red [Close] button - your function is
automatically saved.
32. In cell A7 type =myrnd(100) and press [Enter] (you should get a random number
between 0 and 99.99)

If you want to use an Excel function in Visual Basic then you have to declare it as such.
This next exercise works out the area of a circle using the PI() function.

33. Click on the Developer tab and click on [Visual Basic]

 34. Click below the existing function and type function myarea(r) then press [Enter].
Notice that Visual Basic puts a line between your functions and again puts the End
Function code in for you.
35. Now type in the calculation: myarea = worksheetfunction.pi * r *r

You'll find that as soon as you press the period (full stop) after worksheetfunction that a list of
the available functions appears. You can either select (pi) from the list or continue typing.

36. Close the Editor by clicking on the red [Close] button.

37. In cell A8 type =myarea(10) then press [Enter] - you have the area of a circle with a
radius of 10

You can have as many functions as you like. By default, they are stored within a particular file
but Excel does let you save them to be accessible to any Excel file.

Click on the File tab, save the file to your computer as is a ‘Macro Enabled Workbook’ to make
sure your macros save, then answer the functions D assessment based on your file and/or
comprehension of the lecture.