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.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.