Skip to main content
Omni supports position functions typically found in most spreadsheet applications such as Google Sheets. This reference details the position functions supported by Omni, which can be used to return data from specified points in a result table.

​
INDEX

Returns a value or the reference to a value from an array. This function accepts two arguments:
  • reference - The range of cells from which values are returned
  • position_number - The index of the array to be returned from within the reference. If set to 0, values for the entire range will be returned.
For more information, refer to the Google Sheets documentation.
Syntax
Example
There is not full parity of functionality between this function in Google Sheets and Omni. Refer to the Google Sheets documentation for more information.

​
MATCH

Returns the relative position of an item in a range that matches a specified value. Note: This function will not return the matched value. For more information, refer to the Google Sheets documentation.
Syntax
Example
There is not full parity of functionality between this function in Google Sheets and Omni. Refer to the Google Sheets documentation for more information.

​
PIVOT

In a workbook results table, add a static PIVOT() function by entering "=" and selecting CMD + click (or Control + click) on the reference cell.
Returns a value from a specific pivot in the results table. This function accepts two arguments:
  • ref - The reference to the range of data that includes the column from which to extract values. This only accepts a single column reference.
  • column_index - The index number of the column within the referenced range from which to retrieve values.
Syntax
Example

​
PIVOTINDEX

Returns the index of the current pivot. This function does not require any arguments. The values returned for this function are based on the position the pivoted column is on the table. For example, "Cancelled" = index 1, "Complete" = index 2, etc.
Syntax
Example

​
PIVOTOFFSET

Returns a pivot value offset from the current pivot column. This function accepts the following arguments:
  • value_ref - Reference to the starting cell or range
  • row_offset - Number of rows to move. Positive = down, negative = up.
  • column_offset - Number of columns to move. Positive = down, negative = up.
  • num_rows - Optional. Number of rows in the returned range. Default is 1.
  • num_cols - Optional. Number of columns in the returned range. Default is 1.
Syntax
Example - Returns the value located 2 rows down and 1 column to the left from cell A1

​
PIVOTROW

Returns the values of a specified row in a pivot. Can be used to aggregate the values in a row, such as summing all values in the row.
Syntax
Example

​
SWITCH

Evaluates an expression against a list of values and returns the result corresponding to the first matching value. Values and results are provided in pairs, and the first matching pair is returned. An optional default argument can be provided as the last argument, which will be used if no matches are found. For more information, refer to the Google Sheets documentation.
Syntax
Example
The following table contains what the result would be for each case (ex: apple) argument:

​
ROW

Returns the current row number. Arguments are not supported. For more information, refer to the Google Sheets documentation.
Syntax
Example

​
VLOOKUP

Searches for a value in a range and returns the value in the specified column. For more information, refer to the Google Sheets documentation.
Syntax
Example

​
XLOOKUP

Searches for a value in a specified range in another query and returns the corresponding value from the range. If multiple rows match the lookup value, the minimum of the return value will be selected. Can be used to perform cross-query analysis on queries in the same workbook. For example, fact look ups or joining time series across workbook tabs. Note XLOOKUP does not work with pivoted data sets, with the exception of period-over-period results, where it will always select the current period. For more information, refer to the Google Sheets documentation.
Syntax
The lookup_range argument must reference a full, single column. For example, 'State Mottos'!A:A but not 'State Mottos'!A:C or 'State Mottos'!A1:A4.
Use the Copy XLookup Reference option in the column header’s menu options to quickly copy an XLOOKUP reference.
Example
This example has two tabs:
  • State Mottos, which contains State and State Motto columns
  • Query, which contains a few columns, including State and a column for the XLOOKUP calculation
In this example, the calculation uses the State column in the Query tab as the lookup. It uses the values in the lookup column to search the State column in the State Mottos tab and find matches. When there’s a match, the value in the Mottos column is returned. Check out the video for a step-by-step walkthrough: xlookup-example