Spreadsheet
WRAPROWS
Converts a row or column of values into a two-dimensional array by row. Syntax WRAPROWS(values; wrap_count; [fill_with]) values: The cell range containing the values to wrap. Eg., A2:A9 wrap_count: The number of cells in a row after which the ...WRAPCOLS
Converts a row or column of values into a two-dimensional array by column. Syntax WRAPCOLS(values; wrap_count; [fill_with]) values: The cell range containing the values to wrap. Eg., A2:A9 wrap_count: The number of cells in a column after which the ...MAKEARRAY
Returns an array of a specified row and column size, by applying LAMBDA. Syntax MAKEARRAY(rows; columns; lambda(row; column; calculation)) rows: The number of rows to create in result array. Eg., 2 columns: The number of columns to create in the ...MAP
Applies a custom LAMBDA function to each value in the given array and returns an array with the result values. Syntax MAP(array; lambda_or_array1; [lambda1_or_array2]...) array: The cell range containing the values given as input. Eg., A2:A8 lambda: ...EXPAND
Resizes a given array into a resultant array with specified number of rows and columns. Syntax EXPAND(array; rows; [columns]; [fill_with]) array: The cell range containing the values given as input. Eg., A2:B9 rows: The number of rows to be present ...REDUCE
Returns a single value by applying LAMBDA to each value in a given array and returning total value in the accumulator. Syntax REDUCE([initial_value]; array; lambda(accumulator; value; calculation)) initial_value: The start value for accumulator. Eg., ...SCAN
Returns an array as a result of scanning all the given values by applying LAMBDA to each value. The result array will be the same size as the source array. Syntax SCAN([initial_value]; array; lambda(accumulator; value; calculation)) initial_value: ...BYROW
Applies LAMBDA to each row in the given array and returns an array of the results. Syntax BYROW(array; lambda(row; calculation)) array: The cell range containing the values given as input. Eg., A2:C8 lambda: The function to be applied to each row in ...BYCOL
Applies LAMBDA to each column in the given array and returns an array of the results. Syntax BYCOL(array; lambda(column; calculation)) array: The cell range containing the values given as input. Eg., A2:C8 lambda: The function to be applied to each ...CHOOSEROWS
Returns the specified rows from a given array. Syntax CHOOSEROWS(array; row; [row1];...) array: The cell range containing the rows to return. E.g. A2:C9row: The row(s) to return. E.g. 2 Examples Formula =CHOOSEROWS(A2:C9;2;4;6) ...CHOOSECOLS
Returns the specified columns from a given array. Syntax CHOOSECOLS(array; column; [column1];...) array: The cell range containing the columns to return. E.g. A2:C9 column: The column(s) to return. E.g. 2 Examples Formula ...TAKE
Returns the specified number of continuous rows or columns from the start or end of the given array. Syntax TAKE(array; rows; [columns]) array: The range of cells containing the input values. E.g. A2:C9 rows: The number of rows to return. ...DROP
Returns the given array after removing a specified number of rows and columns. Syntax DROP(array; rows; [columns]) array: The range of cells containing the input values. E.g. A2:C9 rows: The number of rows to exclude. Positive values exclude rows ...TOROW
Transforms a given array into a single row. Syntax TOROW(array; [ignore]; [direction]) array: The range of cells containing the values to transform. Eg., A2:C3 ignore: Specifies the values to ignore in the given array. Defaults to 0 if omitted. 0 ...VSTACK
Combines the given set of arrays into a single table with each array appended to the bottom of the previous array. Syntax VSTACK(array1; [array2];...) array: The range of cells containing the values to append. Eg., A3:B7 Examples Formula ...TOCOL
Transforms the given array into a single column. Syntax TOCOL(array; [ignore]; [direction]) array: The range of cells containing the values to transform. Eg., A2:C3 ignore: Specifies the values to ignore in the given array. Defaults to 0 if ...HSTACK
Combines the given set of arrays into a single table with each array appended to the right of the previous array. Syntax HSTACK(array1; [array2];...) array: The range of cells containing the values to append. Eg., B2:G3 Examples Formula ...LAMBDA
Create custom functions with personalized calculations that can be reused within the spreadsheet using the LAMBDA function. Syntax LAMBDA(name; cal_or_name; [name]; [name2]...) name: The variable to be used in the defined calculation. cal: The ...LET
Allows defining of names, values and calculations within the function. These names can only be used within the formula. Syntax LET(name, value, cal_or_name, [value], [cal_or_name], [value2], [cal_or_name2]...) name: The names to which you can ...IMPORTRANGE
Imports values from a given range of cells from another specified spreadsheet (workbook) in Zoho Sheet. Syntax IMPORTRANGE( spreadsheet_url; range ) spreadsheet_url : The link of the source spreadsheet from where the range is to be imported. Eg., " ...TOP
Returns top part/whole array from the top of given data_range after sorting it in descending order. Syntax TOP(data_range; sort_index; [result_index]; [result_sets]) data_range: The array or range containing the input data. E.g. A2:D8 sort_index: ...BOTTOM
Returns part/whole array from the top of given data_range after sorting it in ascending order. Syntax BOTTOM(data_range; sort_index; [result_index]; [result_sets]) data_range: The array or range containing the input data. E.g. A2:D8 sort_index: ...SEQUENCE
Returns a list of sequential numbers as an array in the provided cell range. Syntax SEQUENCE(row; [column]; [first_value]; [step]) row: Number of rows to be filled with the sequential array. Eg., 2 column: Number of columns to be filled with the ...SORTBY
Sorts a given array or range of values based on a corresponding array or range, and the specified criteria. It is a dynamic array function, where the result array may increase or decrease in size based on the source range. Syntax SORTBY(range; ...SORT
Sorts a given array or range of values based on the specified criteria. It is a dynamic array function, where the result array may increase or decrease in size based on the source range. Syntax SORT(range; [sort_by]; [order]; [direction]) range: ...XMATCH
Returns the relative position of the given value in a given range. XMATCH is an advanced version of the MATCH function. Syntax XMATCH(search_item; search_region; [match_mode]; [search_mode]) search_item: The value to search for in the ...XLOOKUP
Tests the search_table for the given lookup_value and returns a corresponding value from the result_table. XLOOKUP is an advanced version of the VLOOKUP and HLOOKUP functions that searches both vertically and horizontally. Syntax ...VLOOKUP
VLOOKUP checks the first column of the given data table for the specified value and returns a corresponding value, in that row, based on the column given. Syntax VLOOKUP( lookup_value; data_table; column_index; [mode] ) lookup_value : The value ...SHEET
Returns the sheet number for a given cell reference. Syntax SHEET(cell_ref) cell_ref: The cell reference whose sheet number you want. If omitted, it returns the sheet number of the current cell containing the formula. E.g. SHEET.A1 Remarks The ...ROWS
Returns the number of rows in a given range. Syntax ROWS(cell_ref) cell_ref: The cell or range within which you want to calculate the number of rows. E.g. A1:C8 Examples Formula Result =ROWS( A1:C8 ) 8 =ROWS( A5:C5 ) 1 =ROWS(Sales_data) 9 Get ...ROW
Returns the row number for a given cell reference. Syntax ROW(cell_ref) cell_ref: The reference of the cell whose row number you want to obtain. If omitted, it returns the row number of the current cell containing the formula. E.g. A5:C5 Remarks ...OFFSET
Returns a reference that is a given number of rows and columns away from the specified cell/range. Syntax OFFSET(reference; row_offset; col_offset; [new_height]; [new_width]) reference: The starting cell/range from which the given offset ...MATCH
Returns the relative position of the given value in a given range. Syntax MATCH(search_item; search_region; [match_type]) search_item: The value to search for in the given range. E.g. "Ming Yin" search_region: The row/column to search. E.g. C2:C7 ...LOOKUP
Searches for a given value in a sorted row/column and returns a corresponding value from the given row/column. If more than one exact match appears, LOOKUP takes the value in a random order. If no exact match appears, LOOKUP takes the next smallest ...INDIRECT
Returns a cell reference, given a text string. The obtained reference is evaluated and the function finally returns the cell value. Syntax INDIRECT(text_ref; [ref_type]) text_ref: The string that represents the cell reference to evaluate. E.g. D3 ...INDEX
Returns a value from a table, given a row and column number. Syntax INDEX(data_table; row; column; area_number) data_table: The cell range to test. E.g. A1:D8 row: Row number of the cell containing the value to return, in the given data_table. ...IMAGE
Inserts the given image into the specified cell. Syntax IMAGE(url, [mode]) url: The url of the image to insert. E.g. " https://images.pexels.com/photos/459225/pexels-photo-459225.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500" mode: Indicates the ...HYPERLINK
Creates a hyperlink inside a cell that, when clicked, opens the address specified. Syntax HYPERLINK(link_address; [cell_text]) link_address: The URL or address to open. In case of a cell reference, use "#cell_reference". E.g. " www.zoho.com" ...HLOOKUP
HLOOKUP checks the first row of the given data table for the specified value and returns a corresponding value in that column, based on the row given. Syntax HLOOKUP(lookup_value; data_table; row_index; [mode]) lookup_value: The value to look for ...ERRORTYPE
Returns a number corresponding to each error value. It gives #N/A if there is no error in the given reference. Syntax ERRORTYPE(reference) reference: The cell reference given as input for the function. E.g. A3 Error Type Result #CIRCULARREF! ...Next page
Access your files securely from anywhere
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Bigin?
New to Zoho Survey?
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
Zoho Creator Resources
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?