Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

(no description)

File Size: 968 lines (39 kb)
Included or required:0 times
Referenced: 0 times
Includes or requires: 0 files

Defines 1 class

LookupRef:: (17 methods):
  cellAddress()
  COLUMN()
  COLUMNS()
  ROW()
  ROWS()
  HYPERLINK()
  INDIRECT()
  OFFSET()
  CHOOSE()
  MATCH()
  INDEX()
  TRANSPOSE()
  vlookupSort()
  VLOOKUP()
  HLOOKUP()
  LOOKUP()
  FORMULATEXT()


Class: LookupRef  - X-Ref

cellAddress($row, $column, $relativity = 1, $referenceStyle = true, $sheetText = '')   X-Ref
CELL_ADDRESS.

Creates a cell address as text, given specified row and column numbers.

Excel Function:
=ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])

param: mixed $row Row number to use in the cell reference
param: mixed $column Column number to use in the cell reference
param: int $relativity Flag indicating the type of reference to return
param: bool $referenceStyle A logical value that specifies the A1 or R1C1 reference style.
param: string $sheetText Optional Name of worksheet to use
return: string

COLUMN($cellAddress = null)   X-Ref
COLUMN.

Returns the column number of the given cell reference
If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array.
If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
reference of the cell in which the COLUMN function appears; otherwise this function returns 0.

Excel Function:
=COLUMN([cellAddress])

param: null|array|string $cellAddress A reference to a range of cells for which you want the column numbers
return: int|int[]

COLUMNS($cellAddress = null)   X-Ref
COLUMNS.

Returns the number of columns in an array or reference.

Excel Function:
=COLUMNS(cellAddress)

param: null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns
return: int|string The number of columns in cellAddress, or a string if arguments are invalid

ROW($cellAddress = null)   X-Ref
ROW.

Returns the row number of the given cell reference
If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array.
If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
reference of the cell in which the ROW function appears; otherwise this function returns 0.

Excel Function:
=ROW([cellAddress])

param: null|array|string $cellAddress A reference to a range of cells for which you want the row numbers
return: int|mixed[]|string

ROWS($cellAddress = null)   X-Ref
ROWS.

Returns the number of rows in an array or reference.

Excel Function:
=ROWS(cellAddress)

param: null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows
return: int|string The number of rows in cellAddress, or a string if arguments are invalid

HYPERLINK($linkURL = '', $displayName = null, ?Cell $pCell = null)   X-Ref
HYPERLINK.

Excel Function:
=HYPERLINK(linkURL,displayName)

param: string $linkURL Value to check, is also the value returned when no error
param: string $displayName Value to return when testValue is an error condition
param: Cell $pCell The cell to set the hyperlink in
return: mixed The value of $displayName (or $linkURL if $displayName was blank)

INDIRECT($cellAddress = null, ?Cell $pCell = null)   X-Ref
INDIRECT.

Returns the reference specified by a text string.
References are immediately evaluated to display their contents.

Excel Function:
=INDIRECT(cellAddress)

NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010

param: null|array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)
param: Cell $pCell The current cell (containing this formula)
return: mixed The cells referenced by cellAddress

OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $pCell = null)   X-Ref
OFFSET.

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.

Excel Function:
=OFFSET(cellAddress, rows, cols, [height], [width])

param: null|string $cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
param: mixed $rows The number of rows, up or down, that you want the upper-left cell to refer to.
param: mixed $columns The number of columns, to the left or right, that you want the upper-left cell of the result
param: mixed $height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
param: mixed $width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
return: string A reference to a cell or range of cells

CHOOSE(...$chooseArgs)   X-Ref
CHOOSE.

Uses lookup_value to return a value from the list of value arguments.
Use CHOOSE to select one of up to 254 values based on the lookup_value.

Excel Function:
=CHOOSE(index_num, value1, [value2], ...)

return: mixed The selected value

MATCH($lookupValue, $lookupArray, $matchType = 1)   X-Ref
MATCH.

The MATCH function searches for a specified item in a range of cells

Excel Function:
=MATCH(lookup_value, lookup_array, [match_type])

param: mixed $lookupValue The value that you want to match in lookup_array
param: mixed $lookupArray The range of cells being searched
param: mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below.
return: int|string The relative position of the found item

INDEX($arrayValues, $rowNum = 0, $columnNum = 0)   X-Ref
INDEX.

Uses an index to choose a value from a reference or array

Excel Function:
=INDEX(range_array, row_num, [column_num])

param: mixed $arrayValues A range of cells or an array constant
param: mixed $rowNum The row in array from which to return a value. If row_num is omitted, column_num is required.
param: mixed $columnNum The column in array from which to return a value. If column_num is omitted, row_num is required.
return: mixed the value of a specified cell or array of cells

TRANSPOSE($matrixData)   X-Ref
TRANSPOSE.

param: array $matrixData A matrix of values
return: array

vlookupSort($a, $b)   X-Ref
No description

VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)   X-Ref
VLOOKUP
The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.

param: mixed $lookup_value The value that you want to match in lookup_array
param: mixed $lookup_array The range of cells being searched
param: mixed $index_number The column number in table_array from which the matching value must be returned. The first column is 1.
param: mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
return: mixed The value of the found cell

HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)   X-Ref
HLOOKUP
The HLOOKUP function searches for value in the top-most row of lookup_array and returns the value in the same column based on the index_number.

param: mixed $lookup_value The value that you want to match in lookup_array
param: mixed $lookup_array The range of cells being searched
param: mixed $index_number The row number in table_array from which the matching value must be returned. The first row is 1.
param: mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
return: mixed The value of the found cell

LOOKUP($lookup_value, $lookup_vector, $result_vector = null)   X-Ref
LOOKUP
The LOOKUP function searches for value either from a one-row or one-column range or from an array.

param: mixed $lookup_value The value that you want to match in lookup_array
param: mixed $lookup_vector The range of cells being searched
param: null|mixed $result_vector The column from which the matching value must be returned
return: mixed The value of the found cell

FORMULATEXT($cellReference = '', ?Cell $pCell = null)   X-Ref
FORMULATEXT.

param: mixed $cellReference The cell to check
param: Cell $pCell The current cell (containing this formula)
return: string