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
Using lookup formulas in Dashpivot helps automate data retrieval, connect related tables, and reduce manual entry errors across your workflows.