Functions that search for a value(I)

AREAS function (ref)

 

Returns the number of classes of adjacent or single cells of a reference.

Example: =AREAS(A1) returns 1, and there is only one area.

 

LOOKUP function (...)

This function searches for a value in a range of a column, a row, or a matrix. You need to indicate the value to search for, where you want to search for it, and where you want to obtain the result.

 

Example: In column B we have a list of freinds' names, and in column C their e-mails. In cell A1 we write the name of the freind who's e-mail we are searching for. In cell E1 we select the LOOKUP function, and we insert the following parametres:

 

"search_value= A1", "comparation_vector= B:B" and "result_vector = C:C". It will return to us the e-mail related to the person being searched.

 

 

HLOOKUP function (lookup_value,table_array,row_index_num,range_lookup)

Searches the first row of the table or matrix of values, and returns the result in the same column from a specific row.

 

Example: Lets imagine the same situation as before with a column with names and another with the e-mails, and we now want it to tell us who is two places below a freind on the list, or the e-mail that is found two rows below where we are currently looking. We use the the HLOOKUP funcion with the following parametres.

"searched for_value= A1" "search_matrix_in= B:C" and "row_indicator=2" IT will return to us the e-mail of the relevant person situated two rows below the search.

 

VLOOKUP function (lookup_value,table_array,col_index_num,range_lookup)

Search for a value in the first column to the left and return the value in the same row from a specific column.

It has the same effect as the the previous function except that this function carries out the search for columns.

 

MATCH function (lookup_value,lookup_array,match_type)

Returns the relative position of an element, which coincides with a value given in a specified order within a matrix.

match_type is the number -1, 0, or 1 and specifies how the value being searched for should coincide with the values within the search_matrix.

If type_of coincidence is 1, COINCIDE will find the highest value that is less than or equal to the searched for_value. The values in the search_matrix need to be placed in ascending order: ...-2, -1, 0, 1, 2; A-Z: FALSE; TRUE.

If type_of coincidence is 0, COINCIDE will find the first value that is exactly equal to the searched for_value. The values in the search_matrix can be in any order.

If type_of coincidence is -1, COINCIDE will find the smallest value that is greater than or equal to the value being searched for. the search_matrix values need to be placed in descending order: TRUE; FALSE; Z-A; ...2; 1; 0; -1; -2; ... and so succesively.

If coincidence_type is omitted, it is presumed that it is 1.

 

Example: =MATCH(23;{"grapes";,23\"pears";45\"apples";55}) returns 2, which is the position where the number 23 is found.

 

 

COLUMN function (reference)

Returns the column number of a reference.

Example: COLUMN(D:H) returns 4, because, the first column of the D:H matrix is the D (column no. 4)

 

COLUMNS function (matrix)

Returns the number of columns that a matrix is composed of.

Example: =COLUMNS(A:C) returns 3, because, the matrix has 3 columns.

 

Continues on the next page...

 

 

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.