menu

Supported Excel Functions

Lookup functions

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:

  • Approximate match: 1
    uses the fact that the elements of the left-most column of a TableArray object are internally sorted either numerically or alphabetically (whichever is applicable) and searches for the TableArray element closest to the lookup_value. This is the default.
  • Exact match: 0
    searches for the exact value in the left-most column of a TableArray object.

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

HLOOKUP(lookup_value, table_array_name, row_index_num, range_lookup)

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:

  • Approximate match: 1
    uses the fact that the elements of the first row of a TableArray object are internally sorted either numerically or alphabetically (whichever is applicable) and searches for the TableArray element closest to the lookup_value. This is the default.
  • Exact match: 0
    searches for the exact value in the first row of a TableArray object.

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

MATCH(lookup_value, lookup_array_name, match_type)

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:

  • Approximate match: 1
    MATCH finds the largest element value that is less than or equal to the lookup_value. The element values in the lookup array will be internally sorted in the ascending order automatically without changing their original order. This is the default.
  • Exact match: 0
    MATCH finds the first element value that is exactly equal to the lookup_value.
  • Approximate match: -1
    MATCH finds the smallest element value that is greater than or equal to the lookup_value. The element values in the lookup array will be internally sorted in the descending order automatically without changing their original 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

INDEX(table_array_name, row_num, column_num)

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

XMATCH(lookup_value, lookup_array_name, [match_mode], [seach_mode])

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.

  • 0Exact match. If none found, return #N/A. This is the default.
  • -1 Exact match. If none found, return the next smaller item.
  • 1 Exact match. If none found, return the next larger item.
  • 2 A wildcard match.
  • 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:

  • 1 Perform a search starting at the first item. This is the default.
  • -1 Perform a search starting at the last item.
  • 2 Perform a binary search even if the lookup_array is not sorted.
  • 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