Syntax
lookup_value Required.
The value you want to look up.
table_array_name Required.
The name of a TableArray object in which the VLOOKUP will search for the lookup_value and the return value. The scope of the search is limited by the left-most column of a TableArray object. The TableArray objects are placed in a HashMap and retrieved by their names during the formula evaluation.
col_index_num Required.
The column number (starting with 1 for the left-most column of a TableArray object) that contains the return value.
range_lookup Optional.
A value that specifies whether you want VLOOKUP to find an approximate or an exact match:
Looks up a value in the left-most column of a TableArray object and then returns a value in the same row from a column you specify in a TableArray object.
If range_lookup is 0 and lookup_value is a text string, you can use the wildcard characters: the question mark (?) and asterisk (*) in the lookup_value argument.
A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
This is the Adaptive Calculation Engine implementation of the Excel VLOOKUP function.
Syntax
lookup_value Required.
The value you want to look up.
table_array_name Required.
The name of a TableArray object in which the HLOOKUP will search for the lookup_value and the return value. The scope of the search is limited by the first row of a TableArray object. The TableArray objects are placed in a HashMap and retrieved by their names during the formula evaluation.
row_index_num Required.
The row number (starting with 1 for the first row of a TableArray object) that contains the return value.
range_lookup Optional.
A value that specifies whether you want HLOOKUP to find an approximate or an exact match:
Looks up a value in the first row of a TableArray object and then returns a value in the same column from a row you specify in a TableArray object.
If range_lookup is 0 and lookup_value is a text string, you can use the wildcard characters: the question mark (?) and asterisk (*) in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
This is the Adaptive Calculation Engine implementation of the Excel HLOOKUP function.
Syntax
lookup_value Required.
The value you want to look up.
lookup_array_name Required.
The name of a lookup array, represented by a single row or a single column of a TableArray object in which MATCH will search for the lookup_value. The TableArray objects are placed in a HashMap and retrieved by their names during the formula evaluation.
match_type Optional.
The number -1, 0, or 1. The match_type argument specifies how MATCH function matches the lookup_value with the values in the lookup array with the elements in any order:
Searches for a specified value in a row or a column of a lookup array, and then returns its position.
If match_type is 0 and lookup_value is a text string, you can use the wildcard characters: the question mark (?) and asterisk (*) in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
This is the Adaptive Calculation Engine implementation of the Excel MATCH function.
Syntax
table_array_name Required.
The name of a TableArray object from which INDEX will retrieve a value or an array. The TableArray objects are placed in a HashMap and retrieved by their names during the formula evaluation.
row_num Required.
Selects the row in a TableArray object from which to retrieve a value.
column_num Required.
Selects the column in a TableArray object from which to retrieve a value.
Returns the value of an element in a TableArray object, selected by the row and the column number (starting with 1 for the first row and for the first column).
If you set the row_num or the column_num to zero, INDEX returns an array of values of the entire column or the entire row respectively. In this case INDEX must be used as an argument of one of the following functions: MAX, MIN, SUM, AVERAGE, MEDIAN, MODE, COUNT, COUNTA, COUNTBLANK, CONCATENATE, and CONCAT or an error will be produced.
This is the Adaptive Calculation Engine implementation of the Excel INDEX function.
Syntax
lookup_value Required.
The lookup value.
lookup_array_name Required.
The name of a single row or single column TableArray object in which the XMATCH will search for the lookup_value. TableArray objects are placed in the HashMap and retrieved by their names during the formula evaluation.
[match_mode] Optional.
Specify the match type.
If the match_mode is 2 and the lookup_value is a text string, you can use the wildcard characters − the question mark (?) and asterisk (*) in the lookup_value argument. A question mark matches any single character, an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
[seach_mode] Optional. Specify the search mode to use:
The lookup_array elements are not required to be sorted for any seach_mode value.
The XMATCH function returns a position of an item in a lookup_array. If a match does not exist, XMATCH can return the position of the closest (approximate) match.
This is the Adaptive Calculation Engine implementation of the Excel XMATCH function.
In this XMATCH implementation the optional seach_mode parameter has a slightly different set of allowed values than the original one because a TableArray object used as the lookup_array is automatically sorted in the applicable order and a binary search is applied whenever possible.
The bellow examples were constructed based on the XMATCH examples published on the Microsoft Office Support side