Skip to main content

​
INDEX

Returns the content of a cell, specified by row and column offset. This function accepts three arguments:
  • reference - The range of cells from which values are returned
  • row - Optional. The index of the row to be returned from within the reference. If set to 0, values for the entire row will be returned.
  • column - Optional. The index of the column to be returned from within the reference. If set to 0, values for the entire column will be returned.
For more information, refer to the Google Sheets documentation. 
INDEX(range, row, column)
Example 
INDEX(A2:A25, 12)INDEX(A2:A25, 1, 2)
Limitations 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 returned the matched value. For more information, refer to the Google Sheets documentation. 
MATCH(search_key, range)
Example 
MATCH("Jane", A10:A25)
Limitations 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.
PIVOT(ref, column_index)
Example 
PIVOT(A2, 3)

​
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. 
PIVOTINDEX()
Example 
PIVOTINDEX()

​
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.
PIVOTOFFSET(value_ref, row_offset, column_offset, num_rows, num_cols)
Example 
# Returns the value located 2 rows down and 1 column to the left from cell A1PIVOTOFFSET(A1, 2, -1)

​
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. 
PIVOTROW()
Example 
PIVOTROW(A1)               # Returns values in row A1SUM(PIVOTROW(A1))          # Sums all values in row A1

​
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. 
SWITCH(expression, case1, result1, [case2, result2, ...], [default] )
Example 
SWITCH(A1, "apple", "fruit", "banana", "fruit", "carrot", "vegetable", "unknown")
The following table contains what the result would be for each case (ex: apple) argument:
ValueResult
applefruit
bananafruit
carrotvegetable
Any other valueunknown

​
ROW

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

​
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. 
VLOOKUP(lookup_value, lookup_range, column_number)
Example 
VLOOKUP(C1,'State Mottos'!A:A, 2)

​
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. 
XLOOKUP(lookup_value, <query_tab_name>!<lookup_range>, <query_tab_name>!<return_range>)
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 
XLOOKUP(C1,'State Mottos'!A:A,'State Mottos'!B:B)
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