| Formulas | Description | Examples |
|---|---|---|
| AREAS | Syntax: AREAS(ref) This function returns the number of ranges in a reference. Reference is a range of cells. |
=AREAS(A1:C3) |
| CHOOSE | Syntax: Choose(position, value1, value2, ...) Choose a value from a list of values. Position is position number in the list of values to return. It must be a number between 1 and 29. |
=CHOOSE(2, "first", "second", "third") =CHOOSE(6, a1,a2, a3) |
| COLUMN | Syntax: COLUMN(reference) Column function returns the column number of a cell reference. |
=COLUMN() =COLUMN(B4:B6) |
| COLUMNS | Syntax: COLUMNS(reference) Columns function returns the number of columns in a cell reference. |
=COLUMNS(A4:B6) |
| HLOOKUP | Syntax: HLOOKUP(lookup_value, tabe_array, row_index, [not_exact_match]) Search for value in the top row of tabe_array and returns the value in the same column based on the row_index. |
=HLOOKUP("age", A1:D5, 3, TRUE) |
| HYPERLINK | Syntax: HYPERLINK(link_location,friendly_name) Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. |
=hyperlink("http://www.cubedrive.com") =hyperlink("Sheet2!A1", "Link to sheet2") |
| INDEX | Syntax: INDEX(reference, row, column) This function returns either the value or the reference to a value from a table or range. array is a range of cells or table. row is the row number in the array to use to return the value. column is optional. It is the column number in the array to use to return the value. |
=INDEX(A1:D5, 4, 1) |
| LOOKUP | Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector]) The LOOKUP function returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form. |
=LOOKUP(10251, A1:A6, B1:B6) |
| MATCH | Syntax: MATCH(lookup_value, lookup_array, [match_type]) Search for a specified item in a range of cells, and then returns the relative position of that item in the range. The match_type can be 1 (default), 0, -1. |
=MATCH(29, A1:D5, 1) =MATCH("*ge", G498:G501, 0) |
| OFFSET | Syntax: OFFSET(reference, rows, cols, [height], [width]) This function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned. |
=OFFSET(D3,3,-2,1,1) =SUM(OFFSET(D3:F5,3,-2, 3, 3)) |
| ROW | Syntax: ROW(reference) Row function returns the row number of a cell reference. |
= ROW () = ROW (A1:A2) |
| ROWS | Syntax: ROWS(reference) Rows function returns the number of rows in a cell reference. |
=ROWS(A4:B6) |
| VLOOKUP | Syntax: VLOOKUP(lookup_value, tabe_array, col_index, [not_exact_match]) Search for value in the left-most column of tabe_array and returns the value in the same row based on the col_index. |
=VLOOKUP(1,A2:A8,2) =VLOOKUP(0.1,A1:F10,3,TRUE) |