Home » Spreadsheets » Searchable Spreadsheet

Searchable Spreadsheet

Searchable Spreadsheet – Vamos a ver algunas funciones interesantes que podemos aplicar para buscar un dato que no conocemos a partir de otro que sí conocemos. Imaginemos que tenemos los siguientes datos asociados a las calificaciones de unos alumnos:

creating a searchable database website and creating a searchable database in excel

creating a searchable database website and creating a searchable database in excel

Imaginemos también que queremos extraer de esta tabla la calificación del alumno 2 (que está en la celda B4). Para ello tendríamos que buscar en la columna 5 del rango B3:F6 el número de orden en el que se encuentra el alumno 2. Se puede utilizar para ello la función BUSCARV y escribir

=BUSCARV(B4;B3:F6;5)

Algunas de las funciones que tiene OOo Calc para búsqueda de datos se recogen en la siguiente tabla.

Function Description
= VLOOKUP (Search_value; Array_where_to look; column_indicator; sort) This function locates a value in a range of cells and, if it finds it, returns the value stored in the cell corresponding to the indicated column of the same row as the value sought.
= SEARCHH (Search_value; Array_where_to look; show_flag; sort) This function locates a value in a range of cells and, if it finds it, returns the value stored in the cell corresponding to the indicated row of the same column as the value sought.
= MATCH (Search_value; Vector_where_to look; 0) Find the value in the indicated range and return the position. The rank can only have one row or one column
= INDEX (Matrix; Row; Column) Returns the value contained in the intersection cell of the row and column number.

if we write = HLOOKUP (F4; B3: F6; 3), it returns “Recover: N1 N2 N3”
if we write = MATCH (B4; B6: F6; 0), returns 4
if we write = INDEX (B3: F6; 4; 2), returns 6

Searchable Spreadsheet

Searchable Spreadsheet

Searchable Spreadsheet

Example: We are now going to build a book with two sheets to make an invoice. The first page will be where the invoice will be incorporated. The idea is to type only the product code, the applicable VAT rate and the amount of product to be invoiced. The cells will be filled automatically with the unit type of the product, the unit price and the description of the product. These data will be included in sheet 2 of our book.

When generating a new invoice, as we have said before, we will only have to write the product code, the applicable VAT code and the product quantity. The other cells will be calculated automatically.

Gallery of Searchable Spreadsheet

In addition to the image above, you can see a gallery of Searchable Spreadsheet below.

( Click Image to Enlarge )

 

See More!

 

Tag: Searchable Spreadsheet, Spreadsheet

Other Collections of Searchable Spreadsheet

Searchable Document Repository And Searchable Website DatabaseSearchable Web Database And Searchable Database SoftwareSearchable Scanning Software And Searchable Database OnlineBuilding A Searchable Database And Searchable Online DatabaseSearchable Database Website Template And Searchable Pdf DatabaseSearchable SpreadsheetSearchable Google Spreadsheet And Searchable Spreadsheetsearchable database website and searchable document databasesearchable database app and searchable patent databaseSearchable Excel Database Template And Create Searchable Database Google SpreadsheetSearchable Database Website Template And Searchable Map TemplateSearchable Knowledge Base And Searchable Resume DatabaseDatabase Search Website Template And Searchable Database Templatecreating a searchable database website and creating a searchable database in excelCreating A Searchable Database And Searchable Pdf Library