The objective of this exercise is to place formulas in cells B3, B4 and B5 of the Search worksheet so that a SIN entered in cell B2 will produce the other three values found on the same row of the Personal worksheet. The formula entered in cell B3 must be such that it can be copied to B4 and B5 without further editing. Let Excel use relative cell references to produce the correct result.
The formula to place in cell B3 of the Search worksheet will have the following structure:
=INDEX(array, MATCH(locate row), MATCH(locate column))
where array is practically the entire Personal worksheet (see the already defined named ranges provided in Lab6_Ex7.xls).
MATCH(locate row)
returns the position of the SIN (cell B2 in the Search worksheet) in column D of the Personal worksheet. This gives the row index into the array.
MATCH(locate column)
returns the position of the corresponding label in column A of the Search worksheet in the titles found across the first row of the Personal worksheet. This gives the column index of the desired component. Column 1 is the Student #, column 2 is the Last Name, and column 3 is the First Name.
Carefully study the online documentation for MATCH. Every function has online documentation, just follow the link "help on this function" found at the bottom left of the "Insert Function" dialog box encountered when selecting a function. All our uses of MATCH use a match type of 0 (zero). There are two other match types: −1 and +1. Note what each match type gives you.