"use strict";
/***************************************************
* Licensed Materials - Property of HCL.
* (c)Copyright HCL America, Inc. 2023-2024
****************************************************/
Object.defineProperty(exports, "__esModule", { value: true });
exports.choose = exports.columns = exports.column = exports.row = exports.rows = exports.transpose = exports.index = exports.lookup = exports.hLookup = exports.vLookup = exports.match = exports._openFormula = void 0;
const tslib_1 = require("tslib");
/**
* @file Lookup Reference
* @module lookup-reference
* @category Lookup Reference
*/
const openf = tslib_1.__importStar(require("../openformula/lookup-reference"));
exports._openFormula = openf;
const error = tslib_1.__importStar(require("../utils/error"));
//-----------------------------
// Open Formula lookup-reference
/**
* Looks up values in a reference or array.
*
* Category: Lookup and reference
*
* @param {*} lookup_value The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.The lookup_value argument can be a value (number, text, or logical value) or a value reference to a number, text, or logical value.
* @param {Array} lookup_array The range of values being searched.
* @param {number} match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
* @returns {number | Error} the index of the value you are attempting to lookup via match_type, or it returns an error.
* @example match(1, [[0], [1], [2], [3], [4]]) //returns 2
* @example match('*mc', ['jima', 'jimb', 'jimc', 'bernie'], 0) //returns 3
*
*/
function match(lookup_value, lookup_array, match_type = 1) {
return openf.MATCH(lookup_value, lookup_array, match_type);
}
exports.match = match;
/**
* Looks in the first column of an array and moves across the row to return the value of a value.
*
* Category: Lookup and reference
*
* @param {*} lookup_value The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.
* @param {Array.Array} table_array A table of information in which data is looked up. Use a reference to a range or a range name.
* @param {number} col_index_num The row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, VLOOKUP returns the #REF! error value.
* @param {boolean} [range_lookup] Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
* @returns {* | Error} the value in the table_array second column based on the index of the value looked up in the first column.
* @example rosetta.vLookup('jim', [['jam', 2], ['jim', 4]], 2, false) //returns 4
* @example rosetta.vLookup(1.1, [[0, 'A'], [1, 'B'], [2, 'C'], [1, 'D']], 2, true) //returns 'B'
*/
function vLookup(lookup_value, table_array, col_index_num, range_lookup) {
return openf.VLOOKUP(lookup_value, table_array, col_index_num, range_lookup);
}
exports.vLookup = vLookup;
/**
* Looks in the top row of an array and returns the value of the indicated value.
*
* Category: Lookup and reference
*
* @param {*} lookup_value The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.
* @param {Array.Array} table_array A table of information in which data is looked up. Use a reference to a range or a range name.
* @param {number} row_index_num The row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
* @param {boolean} [range_lookup] Optional. A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
* @returns {* | Error} the value in the table_array second column based on the index of the value looked up in the first column.
* @example rosetta.hLookup(0, [[1, 0], ['jim', 'jam']], 2, false) //returns'jam'
* @example rosetta.hLookup(1.4, [[0, 1, 2, 1], ['A', 'B', 'C', 'D']], 2) //returns 'B'
*/
function hLookup(lookup_value, table_array, row_index_num, range_lookup) {
return openf.HLOOKUP(lookup_value, table_array, row_index_num, range_lookup);
}
exports.hLookup = hLookup;
/**
* Looks up values in a vector or array.
*
* Category: Lookup and reference
*
* @param {*} lookup_value A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
- If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
- If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
* @param {Array} array A range of values that contains text, numbers, or logical values that you want to compare with lookup_value. The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.
* @param {Array} [result_array] Optional. A range that contains only one row or column. The result_array argument must be the same size as lookup_value. It has to be the same size.
* @returns {* | Error} the value in the result_array at the same index from the first array that is closest to the lookup_value
* @example lookup('Jack', ['Jim', 'Jack', 'Franck'], ['blue', 'yellow', 'red']) //returns 'yellow'
* @example lookup(0.21, [[0.1, 0.2, 0.3, 0.2]], [['A', 'B', 'C', 'D']]) //returns 'B'
*/
function lookup(lookup_value, array, result_array) {
return openf.LOOKUP(lookup_value, array, result_array);
}
exports.lookup = lookup;
/**
* Uses an index to choose a value from a reference or array.
*
* Category: Lookup and reference
*
* @param {Array} array A range of values or an array constant.
- If array contains only one row or column, the corresponding row_num or column_num argument is optional.
- If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
* @param {number} row_num Required, unless column_num is present. Selects the row in array from which to return a value. If row_num is omitted, column_num is required.
* @param {number} [column_num] Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.
* * @see For futher information, see {@link https://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html#INDEX | www.oasis-open.org}
* @returns {* | Error } returns the value at a given index from a datasource.
* @example rosetta.index([['Banana', 'Apple'],['Strawberry', 'Pineapple']], 2, 1) //returns 'Strawberry'
* @example rosetta.index([['Banana', 'Apple'],['Strawberry', 'Pineapple']], 1, 2) //returns 'Apple'
*/
function index(array, row_num, column_num) {
return openf.INDEX(array, row_num, column_num);
}
exports.index = index;
/**
* Returns the transpose of an array.
*
* Category: Lookup and reference
*
* @param {number[][]} array An array or range of values on a worksheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on. If you're not sure of how to enter an array formula, see Create an array formula.
* @returns {number[][]} the transpose of an array or an error if the arguments are incorrect.
*/
function transpose(array) {
return openf.TRANSPOSE(array);
}
exports.transpose = transpose;
/**
* Returns the number of rows in a reference.
*
* Category: Lookup and reference
*
* @param {number[][]} array An array, an array formula, or a reference to a range of values for which you want the number of rows.
* @returns the number of rows in a reference or an error if the arguments are incorect.
*/
function rows(array) {
if (arguments.length !== 1) {
return error.na;
}
return openf.ROWS(array);
}
exports.rows = rows;
/**
* Returns the row number of a reference.
*
* Category: Lookup and reference
*
* @param {number[][]} reference the value or range of values for which you want the row number.
* @param {number} index
* @returns {number} the row number of a reference or an error if the arguments are incorrect.
*/
function row(reference, index) {
if (arguments.length !== 2) {
return error.na;
}
return openf.ROW(reference, index);
}
exports.row = row;
/**
* Returns the column number of a reference.
*
* Category: Lookup and reference
*
* @param {Array.Array} reference the value or range of values for which you want to return the column number.
* @param {number} index
* @returns {number} the column number of a reference or an error if the arguments are incorrect.
*/
function column(reference, index) {
if (arguments.length !== 2) {
return error.na;
}
return openf.COLUMN(reference, index);
}
exports.column = column;
/**
* Returns the number of columns in a reference.
*
* Category: Lookup and reference
*
* @param {Array.Array} array An array or array formula, or a reference to a range of values for which you want the number of columns.
* @returns {number} the number of columns in a reference or an error if the arguments are incorrect.
*/
function columns(array) {
if (arguments.length !== 1) {
return error.na;
}
return openf.COLUMNS(array);
}
exports.columns = columns;
/**
* Chooses a value from a list of values.
*
* Category: Lookup and reference
*
* @param {number} index_num Specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a value containing a number between 1 and 254. If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on. If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value. If index_num is a fraction, it is truncated to the lowest integer before being used.
- If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
- If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
- If index_num is a fraction, it is truncated to the lowest integer before being used.
* @param {number} index_num the index of the value to return.
* @param {*} args value1, value2, ... Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, value references, defined names, formulas, functions, or text.
* @returns {*} the value at a given index, or an error where it specifies an index that does not exist.
* @example lookup.CHOOSE(3, 'jima', 'jimb', 'jimc') //returns ('jimc')
* @example lookup.CHOOSE(2, 'jima') //returns (error.value)
*/
function choose(index_num, ...args) {
return openf.CHOOSE(index_num, ...args);
}
exports.choose = choose;
//-----------------------------
// Notes @functions lookup-reference
//-----------------------------
// Notes @Commands lookup-reference
//# sourceMappingURL=lookup-reference.js.map