Dashpivot supports Excel-style lookup and reference functions inside Default Tables and Prefilled Tables. These formulas are especially useful for:
Retrieving data from large datasets
Cross-referencing tables
Dynamic report generation
Data validation and error checking
Summing up and examining data
VLOOKUP()
Purpose: Searches vertically and returns a matching value from another column.
Formula to use:
=VLOOKUP(A1,B1:C5,2,FALSE)
Example:
If:
A1 = 3
Range B1:C5 contains IDs in column B and Names in column C
=VLOOKUP(A1,B1:C5,2,FALSE) returns the matching value from column 2.
Notes:
Searches the first column of the table range
Column index determines which column to return
FALSE returns an exact match
HLOOKUP()
Purpose: Searches horizontally and returns a value from a specified row.
Formula to use:
=HLOOKUP(A1,B1:D2,2,FALSE)
Example:
If A1 = "Feb"
and B1:D2 contains months in row 1 and values in row 2
=HLOOKUP(A1,B1:D2,2,FALSE) returns the value from row 2 under "Feb".
INDEX()
Purpose: Returns the value at a specified row and column within a range.
Formula to use:
=INDEX(A1:B3,2,2)
Example:
If range A1:B3 contains data
=INDEX(A1:B3,2,2) returns the value in row 2, column 2 of that range.
MATCH()
Purpose: Returns the position of a value within a range.
Formula to use:
=MATCH(A1,B1:B5,0)
Example:
If A1 = 20
and B1:B5 contains a list of numbers
=MATCH(A1,B1:B5,0) returns the position where 20 is found.
Match types:
0 = Exact match
1 = Ascending order
-1 = Descending order
INDEX + MATCH
Purpose: Performs flexible lookups by combining position matching with value retrieval.
Formula to use:
=INDEX(A1:B5,MATCH(C1,A1:A5,0),2)
Example:
If C1 contains a lookup value
and A1:A5 contains IDs
and B1:B5 contains Names
=INDEX(A1:B5,MATCH(C1,A1:A5,0),2) returns the matching Name.
CHOOSE()
Purpose: Returns a value from a list based on index position.
Formula to use:
=CHOOSE(A1,"Red","Yellow","Blue")
Example:
If A1 = 2
=CHOOSE(A1,"Red","Yellow","Blue") returns Yellow
OFFSET()
Purpose: Returns a value offset from a starting reference.
Formula to use:
=OFFSET(A1,1,1)
Example:
If A1 = 10
and B2 contains 25
=OFFSET(A1,1,1) returns the value in B2.
(1 row down, 1 column right from A1.)
ROW()
Purpose: Returns the row number of a reference.
Formula to use:
=ROW(A5)
Example:
=ROW(A5) returns 5
COLUMN()
Purpose: Returns the column number of a reference.
Formula to use:
=COLUMN(B1)
Example:
=COLUMN(B1) returns 2
ROWS()
Purpose: Returns the number of rows in a range.
Formula to use:
=ROWS(A1:A10)
Example:
=ROWS(A1:A10) returns 10
COLUMNS()
Purpose: Returns the number of columns in a range.
Formula to use:
=COLUMNS(A1:C5)
Example:
=COLUMNS(A1:C5) returns 3
Cross-table lookups
You can use lookup formulas to retrieve values from a different table in the same template. Include the table name in the range reference using this format: TableName!ColumnReference
Example — VLOOKUP across tables:
=VLOOKUP(A1,Materials!A:B,2,FALSE)
Looks up the value in A1 against column A of a table named "Materials" and returns the matching value from column B.
Example — INDEX + MATCH across tables:
=INDEX(Rates!B:B,MATCH(A1,Rates!A:A,0))
Finds A1 in column A of the "Rates" table and returns the corresponding value from column B.
Note: The table name must match exactly as it appears in the template, including capitalisation.
Using lookup formulas in Dashpivot helps automate data retrieval, connect related tables, and reduce manual entry errors across your workflows.