Dashpivot offers formulas that are used to retrieve and search for a specified data from a table, range or data set. These are especially useful for:
Data Retrieval from a Large Data set
Cross-referencing data
Dynamic Report Generation
Error Checking
Data Validation
Summarising Data
Simplifying Data Analysis
Make use of the look up formulas in Dashpivot to streamline your forms and make processes much easier to track.
Function ID | Description | Syntax | Sample | Results | Notes |
CHOOSE | Uses an index to return a value from a list of up to 30 values. | CHOOSE(Index, Value1, ..., Value30) | =CHOOSE(A1, “Red”, “Yellow, “Blue”) *A1 - Number field Given that, A1 = 2 | Returns text, “Yellow” |
|
COLUMN | Returns column number of a given reference or formula reference if argument not provided. | COLUMNS([Reference]) | =COLUMN(A1) | Returns value, 1 (Because A1 is located in the first column) |
|
COLUMNS | Returns the number of columns in the given reference. | COLUMNS(Array) | =COLUMNS(A1:C1) | Returns value, 3 (Because A1 to C1 contains 3 columns) |
|
HLOOKUP | Searches horizontally with reference to adjacent cells to the bottom. | HLOOKUP(Search_Criterion, Array, Index, Sort_Order) | =HLOOKUP("Feb", A1:C2, 2, FALSE) *A1:C2 - All fields except attachment and photos Given that, A2 = 100 B2 = 200 C2 = 300 | Returns value, 200 (Because if this was a table, the value in the second row under Feb is 200) |
|
INDEX | Returns the contents of a cell specified by row and column number. The column number is optional and defaults to 1. | INDEX(Range, Row [, Column]) | =INDEX(A1:B2, 2, 2) *A1:B2 - Number/Text fields Given that, Suppose you have columns A and B and two rows. A1 = 10 A2 = 15 B1 = 20 B2 = 25 | Returns value, 25 (Because the value at the 2nd row and 2nd column is 25) |
|
MATCH | Returns the relative position of an item in an array that matches a specified value. | MATCH(Searchcriterion, Lookuparray, Type) | =MATCH(B1, A1:A3, 0) *A1:A3 - Number field Given that, A1 = 10 A2 = 11 A3 = 12 B1 = 11 | Returns value, 2 (Because the value in B1 is 11, and 11 is found 2nd in the list) | Types:
|
OFFSET | Returns the value of a cell offset by a certain number of rows and columns from a given reference point. | OFFSET(Reference, Rows, Columns, Height, Width) | =OFFSET(A1, 1, 1) *A1:B2 - Text/Number fields Given that, A1 = 1 A2 = 3 B1 = 2 B2 = 4 | Returns value, 4 (Because 1 row down and 1 row to the right is cell B2) |
|
ROW | Returns row number of a given reference or formula reference if argument not provided. | ROW([Reference]) | =ROW(C5) | Returns value, 5 (Because C5 is in the 5th row) |
|
ROWS | Returns the number of rows in the given reference. | ROWS(Array) | =ROWS(A1:A10) | Returns value, 10 (Because there are 10 rows from A1 to A10) |
|
VLOOKUP | Searches vertically with reference to adjacent cells to the right. | VLOOKUP(Search_Criterion, Array, Index, Sort_Order) | =VLOOKUP(3, A1:B2, 2, FALSE) *A1:B2 - Text/Number fields Given that, A1 = 1 A2 = 2 A2 = 3 B1 = Anton B2 =Baxton B3 = Chester | Returns text, Chester (Because Chester has the value of 3 in his first column) |
|