Step by step exercise. Lookup functions.


Objective.

 

Practice the use of the functions availible to search for values in Excel2003.

 

Step by step exercise.

 

1 If you do not have Excel2003 open, open it in order to perform the exercise.

2 Write in column B the names of 10 real or imagined persons, eg John, Paul, Joe, Sally...Every one in a cell, but remember to keep them all in column B.

3 Now in column C write a telephone number next to each name that you introduced before.

We are going to make use of the LOOKUP(), we will use the cell D5 to introduce the name to search and cell D11 to harbour the telephone number of the person being searched for.

4 After this, situate yourself in cell D11 and clic on , select the search and reference category and then the LOOKUP() function.

5 Click on OK.

6 In the lookup_value parameter introduce or select the cell D5, in lookup_vector write "B:B"or select column B, and in the result_vector write "C:C" or select Column C.

7 Insert a value into cell D5, a value found in column B, and see what occurs. The telephone number of the person should appear in cell D11.

We are now to use the TRANSPOSE() function, which you might find a little more complicated.

Remember that to transpose implies changing the rows to columns and vice-versa. That is, if we have data in a row, on transposing it we will have the data in a column.

8 Situate yourself in cell A15 and clic on , selecting the search and reference category and then selecting the TRANSPOSE() function. Click on OK.

In the only parameter that will ask us for "Matrix" we should select the matrix of rows and columns that we are going to transpose.

9 Select the matrix of B1:C10.

The formula is already complete, but we will see that we are advised of an value error, this is due to the formula referring to a matrix and not to a single cell. In order for the transposition to be properly executed we will need to repeat the folmula for each one of the cells to transpose by following these next steps.

10 As from the cell of the formula (A15) it is included, by selecting the cells necessary to cover the transposed matrix, that is, a matrix of 2 rows by 10 columns that correspond with the matrix A15:J16, press F2 and continued with Ctrl+Shift+Enter. In this way the formula will copy the rest of the cells and it will be properly executed..

11 Save the work book in the My exercises folder of the hard drive, naming it Search functions.

12 Close the work book.

 



   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.