Dataset & Lookup Functions

CalcTree provides functions for working with datasets — tabular data created from Python nodes. Use these to extract columns, look up values, and perform conditional aggregations directly in math form

Dataset Functions

Function	Description	CalcTree Example
col(dataset, "column")	Extract a column as a vector	col(sales, "revenue") → [100, 200, 150]
cols(dataset)	Get all column names	cols(sales) → ["id", "revenue", "region"]
nrows(dataset)	Get the number of rows	nrows(sales) → 150

col() also works with 2D arrays and matrices using a 0-based column index: col([[1, 2], [3, 4]], 0) → [1, 3]

---

Lookup Functions

Function	Description	CalcTree Example
VLOOKUP(key, table, colIndex, rangeLookup)	Find a value in the first column and return from another column (1-based)	VLOOKUP("Steel", data, 2, false) → 250
INDEX(array, row, col)	Get a value by row and column position (1-based)	INDEX(data, 3, 2) → 150
MATCH(key, array, matchType)	Find the position of a value in an array (returns 1-based index)	MATCH("Steel", col(data, "material"), 0) → 2

MATCH types

matchType	Behaviour
0	Exact match
1	Largest value ≤ key (array must be ascending)
-1	Smallest value ≥ key (array must be descending)

Tip: Combine INDEX and MATCH for flexible lookups: INDEX(data, MATCH("Steel", col(data, "material"), 0), 3)

---

Conditional Lookup Functions

Filter rows based on a condition and return matching values from a specified column. All column indices are 1-based.

Function	Description	CalcTree Example
vlookup_eq(matrix, value, lookupCol, returnCol)	Where lookupCol equals value	vlookup_eq(data, "North", 1, 3)
vlookup_ne(matrix, value, lookupCol, returnCol)	Where lookupCol does not equal value	vlookup_ne(data, "North", 1, 3)
vlookup_lt(matrix, value, lookupCol, returnCol)	Where lookupCol < value	vlookup_lt(data, 100, 2, 3)
vlookup_le(matrix, value, lookupCol, returnCol)	Where lookupCol ≤ value	vlookup_le(data, 100, 2, 3)
vlookup_gt(matrix, value, lookupCol, returnCol)	Where lookupCol > value	vlookup_gt(data, 100, 2, 3)
vlookup_ge(matrix, value, lookupCol, returnCol)	Where lookupCol ≥ value	vlookup_ge(data, 100, 2, 3)

---

Aggregation Functions

Function	Description	CalcTree Example
SUMIF(range, criteria, sum_range)	Sum values where criteria is met	SUMIF(col(data, "region"), "North", col(data, "revenue")) → 350
AVERAGEIF(range, criteria, average_range)	Average values where criteria is met	AVERAGEIF(col(data, "region"), "North", col(data, "revenue")) → 175
COUNTIF(range, criteria)	Count values matching criteria	COUNTIF(col(data, "status"), "Pass") → 12

If sum_range or average_range is omitted, the function operates on range directly.