http://www.zorba-xquery.com/modules/excel/lookup

import module namespace excel = "http://www.zorba-xquery.com/modules/excel/lookup";

This module implements some Excel 2003 lookup functions.

Daniel Turcanu

xquery version "3.0" encoding "utf-8";

• the XQuery module can be found here.

Imported modules:

• http://www.zorba-xquery.com/modules/excel/math

For more details please also see:

• Excel 2003 Documentation: Lookup Functions

choose

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.

• $index_num The position in the sequence, 1 based.
• $values The sequence of values.

• The value at the index position.

• excel-err:Value if index is smaller than 1 or bigger than the size of sequence.

• http://office.microsoft.com/en-us/excel/HP052090131033.aspx

• choose1.xq

choose

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.

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

• The value at the index position

• excel-err:Value if index is smaller than 1 or bigger than 29

• http://office.microsoft.com/en-us/excel/HP052090131033.aspx

• choose2.xq

hlookup

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.

• $lookup_value 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 the sequence of values, row after row
• $table_width the number of values in a row
• $table_height the number of rows
• $row_index_num the row index, 1 based

• The value found, with original type

• excel-err:Value if the array contains less elements than specified by table_height and table_width
• excel-err:Value if row_index_num is outside the range 1 .. table_height
• excel-err:Value if range_lookup is true and the value searched is smaller than the first value in the header
• excel-err:Value if range_lookup=false and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052091141033.aspx

hlookup

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

• $lookup_value 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 the sequence of values, row after row
• $table_width the number of values in a row
• $table_height the number of rows
• $row_index_num the row index, 1 based
• $range_lookup 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.

• The value found, with original type

• excel-err:Value if the array contains less elements than specified by table_height and table_width
• excel-err:Value if row_index_num is outside the range 1 .. table_height
• excel-err:Value if range_lookup is true and the value searched is smaller than the first value in the header
• excel-err:Value if range_lookup=false and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052091141033.aspx

• hlookup1.xq
• hlookup2.xq
• hlookup3.xq
• hlookup4.xq
• hlookup5.xq

index

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

• $array the sequence of values, row after row
• $array_width the number of values in a row
• $array_height the number of rows
• $row_num the row position of the value, 1 based
• $column_num the column position of the value, 1 based

• The value from x-y in the array

• excel-err:Value if the array contains less elements than specified by table_height and table_width
• excel-err:Ref if row_num is outside the range

• http://office.microsoft.com/en-us/excel/HP052091381033.aspx

• index1.xq
• index2.xq
• index3.xq
• index4.xq
• index5.xq

lookup

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.

• $lookup_value the value to be searched
• $lookup_vector the sequence to be searched, in ascending order.
• $result_vector the sequence containing the result values

• a value from $result_vector

• excel-err:NA if lookup value is smaller than the first value in lookup_vector
• excel-err:NA if position found is outside the result range

• http://office.microsoft.com/en-us/excel/HP052091631033.aspx

• lookup1.xq
• lookup2.xq
• lookup3.xq
• lookup4.xq

lookup

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.

• $lookup_value 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 the array sequence, row after row
• $array_width the number of values in a row
• $array_height the number of rows in the array

• The corresponding value in the last row or column

• excel-err:Value if array contains less values than specified by array_width and array_height or array_width = 0 or array_height = 0
• excel-err:NA if the lookup_value is smaller than the first value in the row or column

• http://office.microsoft.com/en-us/excel/HP052091631033.aspx

• lookup5.xq
• lookup6.xq
• lookup7.xq

match

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.

• $lookup_value value to be searched.
• $sequence the vector where to search the value

• The position of found value

• excel-err:NA for match_type 1 or -1, the lookup_value is smaller or larger than the first value in sequence
• excel-err:Value if range_lookup=0 and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052091681033.aspx

• match1.xq

match

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.

• $lookup_value value to be searched.
• $sequence the vector where to search the value
• $match_type 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.

• The position of found value

• excel-err:NA for match_type 1 or -1, the lookup_value is smaller or larger than the first value in sequence
• excel-err:Value if range_lookup=0 and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052091681033.aspx

• match2.xq
• match3.xq

offset

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.

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

• The sequence specifying the sub-array, row after row

• excel-err:NA rows or cols are negative
• excel-err:NA height or width are smaller than 1
• excel-err:Value reference array contains less elements than specified by reference_height and reference_width
• excel-err:NA the resulted sub-array is not completely contained inside reference array

• http://office.microsoft.com/en-us/excel/HP052092081033.aspx

• offset1.xq
• offset2.xq

offset

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

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

• The sequence specifying the sub-array, row after row

• excel-err:NA rows or cols are negative
• excel-err:NA height or width are smaller than 1
• excel-err:Value reference array contains less elements than specified by reference_height and reference_width
• excel-err:NA the resulted sub-array is not completely contained inside reference array

• http://office.microsoft.com/en-us/excel/HP052092081033.aspx

• offset3.xq
• offset4.xq

transpose

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.

• $array the sequence specifying the array, row after row
• $array_width the number of elements in a row
• $array_height the number of rows in the array

• 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.

• excel-err:Value the array contains less elements than specified by array_width and array_height

• http://office.microsoft.com/en-us/excel/HP052093191033.aspx

• transpose1.xq
• transpose2.xq

vlookup

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.

• $lookup_value 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 the sequence of values, row after row
• $table_width the number of values in a row
• $table_height the number of rows
• $col_index_num the row index, 1 based

• The value found, with original type

• excel-err:Value if the array contains less elements than specified by table_height and table_width
• excel-err:Value if col_index_num is outside the range 1 .. table_height
• excel-err:Value if range_lookup is true and the value searched is smaller than the first value in the first column
• excel-err:Value if range_lookup=false and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052093351033.aspx

• vlookup1.xq
• vlookup2.xq
• vlookup3.xq
• vlookup5.xq

vlookup

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

• $lookup_value 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 the sequence of values, row after row
• $table_width the number of values in a row
• $table_height the number of rows
• $col_index_num the row index, 1 based
• $range_lookup 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.

• The value found, with original type

• excel-err:Value if the array contains less elements than specified by table_height and table_width
• excel-err:Value if col_index_num is outside the range 1 .. table_height
• excel-err:Value if range_lookup is true and the value searched is smaller than the first value in the first column
• excel-err:Value if range_lookup=false and the value cannot be found

• http://office.microsoft.com/en-us/excel/HP052093351033.aspx

• vlookup4.xq