Unit 4.  Formulas and Functions (V)

 Search function

In an Excel sheet it is very important to get the correct data with which to work with the designed formulas. For this there exists a specific group of functions with which to perform data searches.

We understand that a search in itself is when we want to find some kind of information, we do not look for it directly as the data is unknown to us, and so we perform a search of a property or something similar that is know to us to possibly contain what it is that we are looking for. Eg if we are looking for a person we describe their physical aspect, while if we are looking for the tel. number of a restaurant we search in the tel. book for the name of the restaurant. The data that we are looking for is normally unknown to us, but we are able to search for it by using data that is known to us.

These are the availible options in Excel to perform searches:

 Función Descripción Ver Detalle AREAS(reference) Returns the number of areas in a reference LOOKUP(...) Searches for the values of a range in a column or file. HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table MATCH(lookup_value,lookup_array,match_type) Returns the relative position of an item in an array that matches a specified value in a specified order COLUMN(reference) Returns the column number of a reference COLUMNS(array) Returns the number of columns in an array or reference. OFFSET(reference;rows;columns;height;width) Returns a range reference ADDRESS(row_num,column_num,abs_num,a1,sheet_text) Creates a cell address as text, given specified row and column numbers. CHOOSE(index_num;value1;value2;...) Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 29 values based on the index number ROW(reference) Returns the row number ROWS(array) Returns the number of rows HYPERLINK(link_location;friendly_name) Creates a direct access to a document GETPIVOTDATA(data_field;pivot_table;field1;element1;field2;element2...) Extracts data stored in a pivot table. INDEX(array,row_num,column_num) Returns a value or the reference to a value from within a table or range. INDIRECT(ref_text;a1) Returns the reference specified by a text string. References are immediately evaluated to display their contents. TRANSPOSE(array) Returns a vertical range of cells as a horizontal range, or vice versa.
 You can practice some of the functions explained here in Exercises on search functions.
 Page. 4.5
 Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.