Searchable Spreadsheet – Let’s see some interesting functions that can be applied to find a data that we do not know from another that we do know. Imagine that we have the following data associated with the grades of some students:
Let’s also imagine that we want to extract from this table the grade of student 2 (which is in cell B4). For the we would have to look in column 5 of the B3 range: F6 the order number in which the student is 2. You can use for the VARCHARV function and write
= SEARCHV (B4; B3: F6; 5)
Some of the functions that OOo Calc has for data search are listed in the following table.
= LOOKUPV (Search_value; Search_matrix; column_array; sort) This function locates a value in a cell range and, if it finds it, returns the value stored in the cell corresponding to the indicated column of the same row as the searched value.
= LOOKUP (Search_Date; Search_array; row_array; sort) This function locates a value in a cell range and, if found, returns the value stored in the cell corresponding to the indicated row in the same column as the searched value.
= COINCIDIR (Search_value; Search_value; 0) Find the value in the indicated range and return the position. Rank can only have one row or column
= INDEX (Matrix; Row; Column) Returns the value contained in the intersection cell of the row and column number.