1. Zorba 3.0 Documentation
  2. Modules
  3. Zorba
  4. Excel
  5. Lookup

http://zorba.io/modules/excel/lookup

View as XML or JSON.

This module implements some Excel 2003 lookup functions.

Function Summary

choose ($index_num as xs:integer, $values as xs:anyAtomicType*) as xs:anyAtomicType

Uses index_num to return a value from the sequence of value arguments.
choose ($index_num as xs:integer, $value_sequence1 as xs:anyAtomicType*, $value_sequence2 as xs:anyAtomicType*, $value_sequence3 as xs:anyAtomicType*, $value_sequence4 as xs:anyAtomicType*, $value_sequence5 as xs:anyAtomicType*, $value_sequence6 as xs:anyAtomicType*, $value_sequence7 as xs:anyAtomicType*, $value_sequence8 as xs:anyAtomicType*, $value_sequence9 as xs:anyAtomicType*, $value_sequence10 as xs:anyAtomicType*, $value_sequence11 as xs:anyAtomicType*, $value_sequence12 as xs:anyAtomicType*, $value_sequence13 as xs:anyAtomicType*, $value_sequence14 as xs:anyAtomicType*, $value_sequence15 as xs:anyAtomicType*, $value_sequence16 as xs:anyAtomicType*, $value_sequence17 as xs:anyAtomicType*, $value_sequence18 as xs:anyAtomicType*, $value_sequence19 as xs:anyAtomicType*, $value_sequence20 as xs:anyAtomicType*, $value_sequence21 as xs:anyAtomicType*, $value_sequence22 as xs:anyAtomicType*, $value_sequence23 as xs:anyAtomicType*, $value_sequence24 as xs:anyAtomicType*, $value_sequence25 as xs:anyAtomicType*, $value_sequence26 as xs:anyAtomicType*, $value_sequence27 as xs:anyAtomicType*, $value_sequence28 as xs:anyAtomicType*, $value_sequence29 as xs:anyAtomicType*) as xs:anyAtomicType*

Uses index_num to return a sequence from the list of sequences.
hlookup ($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $row_index_num as xs:integer) as xs:anyAtomicType

Same as above, only that range_lookup is defaulted to true.
hlookup ($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $row_index_num as xs:integer, $range_lookup as xs:boolean) as xs:anyAtomicType

Searches for a value in the top row of an array of values, and then returns a value in the same column from a row you specify in the array.
index ($array as xs:anyAtomicType+, $array_height as xs:integer, $array_width as xs:integer, $row_num as xs:integer, $column_num as xs:integer) as xs:anyAtomicType+

Returns a value from within an array.
lookup ($lookup_value as xs:anyAtomicType, $lookup_vector as xs:anyAtomicType+, $result_vector as xs:anyAtomicType+) as xs:anyAtomicType

The Vector form.
lookup ($lookup_value as xs:anyAtomicType, $array as xs:anyAtomicType+, $array_width as xs:integer, $array_height as xs:integer) as xs:anyAtomicType

The Array form.
match ($lookup_value as xs:anyAtomicType, $sequence as xs:anyAtomicType+) as xs:anyAtomicType

Same as above, but match_type is defaulted to 1.
match ($lookup_value as xs:anyAtomicType, $sequence as xs:anyAtomicType+, $match_type as xs:integer) as xs:anyAtomicType

Returns the relative position of an item in a sequence that matches a specified value in a specified order.
offset ($reference as xs:anyAtomicType+, $reference_height as xs:integer, $reference_width as xs:integer, $rows as xs:integer, $cols as xs:integer) as xs:anyAtomicType*

Same as above, only that the sub-array is specified only by rows and cols relative position.
offset ($reference as xs:anyAtomicType+, $reference_height as xs:integer, $reference_width as xs:integer, $rows as xs:integer, $cols as xs:integer, $height as xs:integer, $width as xs:integer) as xs:anyAtomicType*

Returns a sub-array from an array.
transpose ($array as xs:anyAtomicType+, $array_width as xs:integer, $array_height as xs:integer) as xs:anyAtomicType+

Transposes an array.
vlookup ($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $col_index_num as xs:integer) as xs:anyAtomicType

Same as above, with range_lookup defaulted to true.
vlookup ($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $col_index_num as xs:integer, $range_lookup as xs:boolean) as xs:anyAtomicType

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Functions

choose#2

declare  function excel:choose($index_num as xs:integer, $values as xs:anyAtomicType*) as xs:anyAtomicType
Uses index_num to return a value from the sequence of value arguments.

Parameters

index_num as xs:integer
The position in the sequence, 1 based.
values as xs:anyAtomicType
The sequence of values.

Returns

xs:anyAtomicType
The value at the index position.

choose#30

declare  function excel:choose($index_num as xs:integer, $value_sequence1 as xs:anyAtomicType*, $value_sequence2 as xs:anyAtomicType*, $value_sequence3 as xs:anyAtomicType*, $value_sequence4 as xs:anyAtomicType*, $value_sequence5 as xs:anyAtomicType*, $value_sequence6 as xs:anyAtomicType*, $value_sequence7 as xs:anyAtomicType*, $value_sequence8 as xs:anyAtomicType*, $value_sequence9 as xs:anyAtomicType*, $value_sequence10 as xs:anyAtomicType*, $value_sequence11 as xs:anyAtomicType*, $value_sequence12 as xs:anyAtomicType*, $value_sequence13 as xs:anyAtomicType*, $value_sequence14 as xs:anyAtomicType*, $value_sequence15 as xs:anyAtomicType*, $value_sequence16 as xs:anyAtomicType*, $value_sequence17 as xs:anyAtomicType*, $value_sequence18 as xs:anyAtomicType*, $value_sequence19 as xs:anyAtomicType*, $value_sequence20 as xs:anyAtomicType*, $value_sequence21 as xs:anyAtomicType*, $value_sequence22 as xs:anyAtomicType*, $value_sequence23 as xs:anyAtomicType*, $value_sequence24 as xs:anyAtomicType*, $value_sequence25 as xs:anyAtomicType*, $value_sequence26 as xs:anyAtomicType*, $value_sequence27 as xs:anyAtomicType*, $value_sequence28 as xs:anyAtomicType*, $value_sequence29 as xs:anyAtomicType*) as xs:anyAtomicType*
Uses index_num to return a sequence from the list of sequences. Use CHOOSE to select one of up to 29 sequences based on the index number.

Parameters

index_num as xs:integer
the position in the sequence, 1 based
value_sequence1 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence2 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence3 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence4 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence5 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence6 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence7 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence8 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence9 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence10 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence11 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence12 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence13 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence14 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence15 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence16 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence17 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence18 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence19 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence20 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence21 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence22 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence23 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence24 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence25 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence26 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence27 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence28 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.
value_sequence29 as xs:anyAtomicType
a sequence of values. Specify the empty sequence () if you don't need it.

Returns

xs:anyAtomicType*
The value at the index position

hlookup#5

declare  function excel:hlookup($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $row_index_num as xs:integer) as xs:anyAtomicType
Same as above, only that range_lookup is defaulted to true. That is, this Hlookup looks for the approximate value and the first row must be ordered ascending.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched. Allowed types are numeric, string, boolean.

Boolean values are compared only with booleans. Numbers are compared only with numbers, if range_lookup is not zero. The other types are converted to string and compared to string value of all values.

table_array as xs:anyAtomicType
the sequence of values, row after row
table_width as xs:integer
the number of values in a row
table_height as xs:integer
the number of rows
row_index_num as xs:integer
the row index, 1 based

Returns

xs:anyAtomicType
The value found, with original type

hlookup#6

declare  function excel:hlookup($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $row_index_num as xs:integer, $range_lookup as xs:boolean) as xs:anyAtomicType
Searches for a value in the top row of an array of values, and then returns a value in the same column from a row you specify in the array.
Array is specified with 3 parameters:
table_array
is a sequence of elements, first row first, then second row and so on
table_width
specifies the number of elements in a row
table_height
specifies the number of rows
The number of elements in table_array must be equal or more than table_width * table_height.

For wildchar matching, the XQuery regex matcher is used.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched. Allowed types are numeric, string, boolean.

Boolean values are compared only with booleans. Numbers are compared only with numbers, if range_lookup is not zero. The other types are converted to string and compared to string value of all values.

table_array as xs:anyAtomicType
the sequence of values, row after row
table_width as xs:integer
the number of values in a row
table_height as xs:integer
the number of rows
row_index_num as xs:integer
the row index, 1 based
range_lookup as xs:boolean
specifies the algorithm to use:
true
find approximative match. First row of array must be sorted in ascending order.
false
find exact match, using xquery regex First row of array can be in any order.

Returns

xs:anyAtomicType
The value found, with original type

index#5

declare  function excel:index($array as xs:anyAtomicType+, $array_height as xs:integer, $array_width as xs:integer, $row_num as xs:integer, $column_num as xs:integer) as xs:anyAtomicType+
Returns a value from within an array.

This is the Array form of the Excel Index function.

Array is specified with 3 parameters:
array
is a sequence of elements, first row first, then second row and so on
array_height
specifies the number of rows
array_width
specifies the number of elements in a row
The number of elements in array must be equal or more than array_width * array_height.

Parameters

array as xs:anyAtomicType
the sequence of values, row after row
array_height as xs:integer
the number of rows
array_width as xs:integer
the number of values in a row
row_num as xs:integer
the row position of the value, 1 based
column_num as xs:integer
the column position of the value, 1 based

Returns

xs:anyAtomicType+
The value from x-y in the array

lookup#3

declare  function excel:lookup($lookup_value as xs:anyAtomicType, $lookup_vector as xs:anyAtomicType+, $result_vector as xs:anyAtomicType+) as xs:anyAtomicType
The Vector form.

Looks in a sequence for a value and return a value from the same position in a second sequence. If the value is not found, then it matches the largest value in lookup_vector that is less than or equal to lookup_value.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched
lookup_vector as xs:anyAtomicType
the sequence to be searched, in ascending order.
result_vector as xs:anyAtomicType
the sequence containing the result values

Returns

xs:anyAtomicType
a value from $result_vector

lookup#4

declare  function excel:lookup($lookup_value as xs:anyAtomicType, $array as xs:anyAtomicType+, $array_width as xs:integer, $array_height as xs:integer) as xs:anyAtomicType
The Array form.

It looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.

If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row.

If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

The values in the first row or first column must be in ascending order.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched. If the value is not found, then it matches the largest value in lookup_vector that is less than or equal to lookup_value.
array as xs:anyAtomicType
the array sequence, row after row
array_width as xs:integer
the number of values in a row
array_height as xs:integer
the number of rows in the array

Returns

xs:anyAtomicType
The corresponding value in the last row or column

match#2

declare  function excel:match($lookup_value as xs:anyAtomicType, $sequence as xs:anyAtomicType+) as xs:anyAtomicType
Same as above, but match_type is defaulted to 1. It finds the largest value that is less than or equal to lookup_value.

Parameters

lookup_value as xs:anyAtomicType
value to be searched.
sequence as xs:anyAtomicType
the vector where to search the value

Returns

xs:anyAtomicType
The position of found value

match#3

declare  function excel:match($lookup_value as xs:anyAtomicType, $sequence as xs:anyAtomicType+, $match_type as xs:integer) as xs:anyAtomicType
Returns the relative position of an item in a sequence that matches a specified value in a specified order. Only for one dimensional vector.

Parameters

lookup_value as xs:anyAtomicType
value to be searched.
sequence as xs:anyAtomicType
the vector where to search the value
match_type as xs:integer
specifies the algorithm used for searching. Possible values:
1
finds the largest value that is less than or equal to lookup_value. Sequence must be in ascending order.
0
finds the first value that is exactly equal to lookup_value.

Sequence can be in any order.

If lookup_value is boolean, then only booleans are compared.

For other types, they are casted to string and then compared using xquery regular expressions. Lookup_value can be a xquery regular expression.

-1
finds the smallest value that is greater than or equal to lookup_value.

Sequence must be in descending order.

Returns

xs:anyAtomicType
The position of found value

offset#5

declare  function excel:offset($reference as xs:anyAtomicType+, $reference_height as xs:integer, $reference_width as xs:integer, $rows as xs:integer, $cols as xs:integer) as xs:anyAtomicType*
Same as above, only that the sub-array is specified only by rows and cols relative position. The sub-array height and width is computed to contain the remaining elements of the array.

Parameters

reference as xs:anyAtomicType
the reference array
reference_height as xs:integer
the number of rows in the reference array
reference_width as xs:integer
the number of elements in the reference array row
rows as xs:integer
the relative row position where the sub-array starts. It must be a positive value, zero relative.
cols as xs:integer
the relative column position where the sub-array starts. It must be a positive value, zero relative.

Returns

xs:anyAtomicType*
The sequence specifying the sub-array, row after row

offset#7

declare  function excel:offset($reference as xs:anyAtomicType+, $reference_height as xs:integer, $reference_width as xs:integer, $rows as xs:integer, $cols as xs:integer, $height as xs:integer, $width as xs:integer) as xs:anyAtomicType*
Returns a sub-array from an array. The inner array must be within the reference array

Parameters

reference as xs:anyAtomicType
the reference array
reference_height as xs:integer
the number of rows in the reference array
reference_width as xs:integer
the number of elements in the reference array row
rows as xs:integer
the relative row position where the sub-array starts. It must be a positive value, zero relative.
cols as xs:integer
the relative column position where the sub-array starts. It must be a positive value, zero relative.
height as xs:integer
the desired height of sub-array. The sub-array must be inside the reference array.
width as xs:integer
the desired width of sub-array. The sub-array must be inside the reference array.

Returns

xs:anyAtomicType*
The sequence specifying the sub-array, row after row

transpose#3

declare  function excel:transpose($array as xs:anyAtomicType+, $array_width as xs:integer, $array_height as xs:integer) as xs:anyAtomicType+
Transposes an array. The rows become columns and vice versa.

Parameters

array as xs:anyAtomicType
the sequence specifying the array, row after row
array_width as xs:integer
the number of elements in a row
array_height as xs:integer
the number of rows in the array

Returns

xs:anyAtomicType+
The transposed array. It will be a sequence specifying an array, row after row. The result width is the input height. The result height is the input width.

vlookup#5

declare  function excel:vlookup($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $col_index_num as xs:integer) as xs:anyAtomicType
Same as above, with range_lookup defaulted to true. It finds the largest value that is less than or equal to lookup_value. First column must be in ascending order.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched. Allowed types are numeric, string, boolean.

Boolean values are compared only with booleans. Numbers are compared only with numbers, if range_lookup is not zero. The other types are converted to string and compared to string value of all values.

table_array as xs:anyAtomicType
the sequence of values, row after row
table_width as xs:integer
the number of values in a row
table_height as xs:integer
the number of rows
col_index_num as xs:integer
the row index, 1 based

Returns

xs:anyAtomicType
The value found, with original type

vlookup#6

declare  function excel:vlookup($lookup_value as xs:anyAtomicType, $table_array as xs:anyAtomicType+, $table_width as xs:integer, $table_height as xs:integer, $col_index_num as xs:integer, $range_lookup as xs:boolean) as xs:anyAtomicType
Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.
Array is specified with 3 parameters:
table_array
is a sequence of elements, first row first, then second row and so on
table_width
specifies the number of elements in a row
table_height
specifies the number of rows
For wildchar matching, the XQuery regex matcher is used.

Parameters

lookup_value as xs:anyAtomicType
the value to be searched. Allowed types are numeric, string, boolean.

Boolean values are compared only with booleans. Numbers are compared only with numbers, if range_lookup is not zero. The other types are converted to string and compared to string value of all values.

table_array as xs:anyAtomicType
the sequence of values, row after row
table_width as xs:integer
the number of values in a row
table_height as xs:integer
the number of rows
col_index_num as xs:integer
the row index, 1 based
range_lookup as xs:boolean
specified the algorithm to use:
true
find approximative match. First column of array must be sorted in ascending order.
false
find exact match, using xquery regex. First column of array can be in any order.

Returns

xs:anyAtomicType
The value found, with original type