ROW()

ROW() - If a single cell is selected, this function returns the row number of a cell reference. If a range is selected, it returns the corresponding row numbers in a one-column array.

Note: If a cell range is selected, the formula must be entered as an array formula (Ctrl>Shift>Enter). If a range is selected and the ROW function is not entered as an array formula, only the the first range cell will be returned.

Example 1: The first example will return the row number a single cell reference.

Cell A1 contains the formula =ROW(A7) which returns a value of 7.

Screen shot



Example 2: The second example will return the row numbers of a one-column array.

Cell A1 contains the formula {=ROW(B1:D5)} See screen shot below for results.

Notice the formula in the second example has braces around it. This is because the the function was entered as an array formula.

Screen shot

HLOOKUP()

HLOOKUP() - This function searches across the top row in an array and returns a value in the same column in a specified row.

Syntax:

HLOOKUP(SearchCriteria;Array;Index;Sorted)

SearchCriteria - What you are wanting to match.
Array - The array you want to search through.
Index - Row number in the array from witch the matching value will be returned.
Sorted - If TRUE, HLOOKUP will look for an approximate match. If FALSE, HLOOKUP will look for an exact match.

Example:



I wanted to find the number of Highlander Hybrid's sold during the month of August. Below is the formula I have in cell "B22".

=HLOOKUP(I1;A1:M19;10;0)

"I1" represents the month I am looking for. I could have typed "August" instead of the cell address.

A1:M19 is the range I am looking through.

10 the row the Highlander Hybrid is in.

O which is the same as false means I want an exact match.

INDEX()

The INDEX function returns the contents of a cell, specified by a row and column number or an optional range name.

Syntax

INDEX(reference;row;column;range)

reference is a cell reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses.

row (optional) represents the row number of the reference range, for which to return a value.

column (optional) represents the column number of the reference range, for which to return a value.

range (optional) represents the index of the subrange if referring to a multiple range.

In the OpenOffice.org Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

Cell A7 contains the following formula =INDEX(A2:D5;3;4)

See screen shot below.

Notice the INDEX function returns the contents of row 3 and column 4 in the selected range. Not, the 3rd row and 4th column of the entire spreadsheet.

Match()

The Match() function returns the relative position of an item in a single row or single column array that meets a specified value.

Syntax

MATCH(search criterion;lookup_array;type)

Search Criterion is the value which is to be searched for in the single-row or single-column array.

lookup_array is the reference searched. A lookup array can be a single row or column, or part of a single row or column.

type

• If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order.

• If Type = -1 it is assumed that the column in sorted in descending order.

• If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the first one found. Only if Type = 0 can you search for regular expressions.

If Type = 1 or the third parameter is missing, the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.

The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character.

For this example, please refer to the screen shot.

Cell A1 contains the following formula =MATCH("Jason";C2:C11;0)

Cell A3 contains the following formula =MATCH("Carey";C14:C23;1)

Cell A5 contains the following formula =MATCH("Carey";H14:H23;-1)

Cell A7 contains the following formula =MATCH("Carey";C2:C11;0)

Cell A9 contains the following formula =MATCH("Ca.*";C2:C11;0)

Screen shot. You click on the image for a larger view.

The first range on the spreadsheet is not sorted in any particular order so cells A1, A7, A9 has type set to 0.

The second range is sorted in ascending order so cell A3 has type set to 1.

The third range is sorted in descending order so cell A5 has type set to -1.

Notice the results of each tell you what row number the search criteria is in for that particular range, not what row it is in in the spreadsheet.

Also, when type is set to 1, the MATCH function returns the row number of the first time it finds the name. When type is set to -1 the MATCH function returns the row number of the second (or last) time it finds the name.

Cell A9 has search criteria "Ca.*". This looks for any match that begins with Ca. It does not matter what comes after Ca.

AREAS

AREAS - This function returns the number of individual ranges that belong to a multiple range. A range can consist of contiguous cells or a single cell.

Syntax:

AREAS(Reference)

Reference - Represents the reference to a cell or cell range.

Example:

=AREAS(A1:B3;F2:G5;J3) will return 3

Screen Shot:

ADDRESS

ADDRESS - this function returns a cell address (reference) as text, according to the specified row and column numbers. You can determine whether the address is interpreted as an absolute address (for example, $A$1) or as a relative address (for example, A1) or in a mixed form (for example, A$1 or $A1). You can also specify the name of the sheet.

Syntax:

ADDRESS(row; column; abs;sheet)

row represents the row number for the cell reference

column represents the column number for the cell reference (the number, not the letter)

abs determines the type of reference:

1: absolute ($A$1)

2: row reference type is absolute; column reference is relative (A$1)

3: row (relative); column (absolute) ($A1)

4: relative (A1)

sheet represents the name of the sheet. It must be placed in double quotes.

Example:

=ADDRESS(10;40;1;"Sheet2") will return Sheet2.$AN$10


Screen shot: