FXLDataLookup and FXLDataPeriod Functions

Introduced in H1.3.0.03 (January 2026).

Fusion XL Data - FXLDataLookup and FXLDataPeriod Formulas

Context

To date, FXLData has been used primarily to generate data cubes in Excel from a Fusion Registry by applying user‑defined selection criteria. The add‑in issues SDMX REST queries to the Fusion Registry and renders the results as a structured grid, typically one series per row (time across columns) or one series per column, so analysts can work with a familiar worksheet layout.

Problem

The current workflow is geared to building full grids/cubes from Fusion Registry; getting a single value or a small ad‑hoc slice often means running a broader query and then indexing/filtering the filled grid, which adds unnecessary steps for simple asks.

Solution

2 New Formulas have been introduced:

• FXLDataLookup retrieves targeted measures directly from an existing root grid, returning a single value, short list, or small table. It also supports simple arithmetic, period wildcards, optional auto‑refresh, and explicit error messages.
• FXLDataPeriod returns the minimum and/or maximum SDMX periods found on a specified root grid so lookups can use dataset bounds without manual date handling (with sheet‑not‑found error handling).

FXLDataLookup Formula

FXLDataLookup reads values from an existing root grid on a specified worksheet and returns the measures for the given series key(s) and period(s) as a

• single value
• short list
• or small table

depending on how many items and periods you pass.

Syntax

FXLDataLookup(items, periods, sheetName, [autoRefresh], [errorMessages])

Parameters

items

• A single series key (text)
• a range of keys
• or an expression combining series keys and numbers with supported operators.

periods

• A single period
• a range of periods
• a wildcard (e.g., "2001*")

sheetName - The worksheet containing the root grid with the expected header/layout [autoRefresh]

• OPTIONAL, Defaults to FALSE
• TRUE/FALSE. When TRUE, forces updates only if Excel is not in automatic calculation mode.

[errorMessages]

• OPTIONAL, Defaults to FALSE
• TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Key Not Found”, “Non‑numeric Value”, “Divide By Zero”, “Invalid Power”, “Parse Error”).

Supported Operators - items parameter

Arithmetic

• + (add)
• - (subtract)
• * (multiply)
• / (divide)
• ^ (power)
• Standard arithmetic precedence applies;
• parentheses can be used to control evaluation order

Unary signs:

• leading -
• and + are supported

Exponent constraint: for ^, the right‑hand exponent must be numeric, either a literal or a cell reference to a numeric value; using a series key on the right‑hand side raises Invalid Power. The left‑hand/base may be a series key.

Wildcards - periods parameter

Period wildcards let you pass a pattern like "2001*"; the function returns that period and all later periods already present in the referenced root grid (e.g., 2001, 2002, 2003).

FXLDataPeriod Formula

FXLDataPeriod scans an existing root grid on a specified worksheet and returns the SDMX period(s) available in that grid, either the minimum, the maximum, or both, depending on the mode you pass.

Syntax

FXLDataPeriod(sheetName, [what], [errorMessages])

Parameters

sheetName - The worksheet containing the root grid with the expected header/layout what

• OPTIONAL, Defaults to BOTH
• MIN - returns the first period
• MAX - returns the last period
• BOTH - returns both the first and last periods

errorMessages

• OPTIONAL, Defaults to FALSE
• TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Grid Not Found”).

FXLDataLookup Examples

The examples below are based on the following root grid sheet.

Basics

Single value (one series + one period)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid")

Result: 99

Why: Pulls the 2002 value for the series key.

---

Multiple series (range) for one period

=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid") Result (row‑wise):

• SC1:STERLING:CHINA → 14
• SG1:STERLING:GERMANY → 333
• EI1:EURO:IRELAND → 15
• EG1:EURO:GERMANY → 26

=FXLDataLookup(RootGrid!G5:G8,"2002","RootGrid") Result (row‑wise):

SC1:STERLING:CHINA → 14 SG1:STERLING:GERMANY → 333 EI1:EURO:IRELAND → 15 EG1:EURO:GERMANY → 26

---

Multiple series × multiple periods (ranges)

=FXLDataLookup(RootGrid!G5:G8, RootGrid!I2:J2, "RootGrid")

Result (examples):

• 2002 → 14, 333, 15, 26
• 2003 → 45, 55, 12, 45

---

Auto‑refresh on (even if Excel calc mode is Manual)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGrid",TRUE)

Result: 99 (same value; it just auto‑refreshes) [FXLDataLookupSamples | Excel]

Arithmetic with series

Addition =FXLDataLookup("SE1:STERLING:ENGLAND+EG1:EURO:GERMANY","2002","RootGrid")

Result: 125 (99 + 26)

---

Subtraction =FXLDataLookup("SE1:STERLING:ENGLAND-EG1:EURO:GERMANY","2002","RootGrid")

Result: 73 (99 − 26)

---

Multiplication =FXLDataLookup("SE1:STERLING:ENGLAND*EG1:EURO:GERMANY","2002","RootGrid")

Result: 2574 (99 × 26)

---

Division =FXLDataLookup("SE1:STERLING:ENGLAND/EG1:EURO:GERMANY","2002","RootGrid")

Result: 3.81 (99 ÷ 26)

Order of operations, exponent, parentheses, unary

Exponent with numeric literal =FXLDataLookup("SE1:STERLING:ENGLAND*2^2","2002","RootGrid")

Result: 396 (99 × 2²)

---

Parentheses respected =FXLDataLookup("(SE1:STERLING:ENGLAND)*(10-2)","2002","RootGrid")

Result: 792 (99 × (10−2))

---

Unary minus =FXLDataLookup("-SE1:STERLING:ENGLAND*2","2002","RootGrid")

Result: -198 (−99 × 2)

---

Unary plus =FXLDataLookup("+(SE1:STERLING:ENGLAND-10000)","2002","RootGrid")

Result: -9901 (+(99−10000))

---

Multiple operands in one go =FXLDataLookup("SE1:STERLING:ENGLAND*SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid")

Result: 9799.62

---

Wildcards (period expansion)

Expand 2001* to all matching periods

=FXLDataLookup(RootGrid!G5:G8, "2001*", "RootGrid")

Result (grid excerpt):

• 2001 → 16, 76, 11, 11
• 2002 → 14, 333, 15, 26
• 2003 → 45, 55, 12, 45

(Order aligns with series in RootGrid!G5:G8)

Period helpers (using SDMXPeriod(...) inside)

Min period

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MIN"), "RootGrid")

Result: 13 (value at min period 2001)

---

Max period

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid","MAX"), "RootGrid")

Result: 45 (value at max period 2003)

---

Min and Max (2‑cell spill)

=FXLDataLookup("SE1:STERLING:ENGLAND", SDMXPeriod("RootGrid"), "RootGrid")

Result (two cells): 13, 45

Errors Sheet not found =FXLDataLookup("SE1:STERLING:ENGLAND","2002","NonExistantRootGrid",FALSE,TRUE)

Result: Sheet Not Found

---

Key not found (typo in series key)

=FXLDataLookup("SE1:STERLING:ENGLANlD","2002","RootGrid",FALSE,TRUE)

Result: Key Not Found

---

Non‑numeric value in grid

=FXLDataLookup("SI1:STERLING:IRELAND","2003","RootGrid",FALSE,TRUE)

Result: Non-numeric Value SI1:STERLING:IRELAND (cell value is g)

---

Grid not found (bad RootGrid layout)

=FXLDataLookup("SE1:STERLING:ENGLAND","2002","RootGridAlternate",FALSE,TRUE)

Result: Grid Not Found (header row not on row 2)

---

Divide by zero

=FXLDataLookup("SE1:STERLING:ENGLAND/(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)

Result: Divide By Zero (26−26 = 0) [FXLDataLookupSamples | Excel]

---

Invalid power

=FXLDataLookup("SE1:STERLING:ENGLAND^(EG1:EURO:GERMANY-26)","2002","RootGrid",FALSE,TRUE)

Result: Invalid Power (exponent expression based on series key is invalid, use a numeric literal or numeric cell)

---

Parse error (operator typo)

=FXLDataLookup("SE1:STERLING:ENGLAND**SE1:STERLING:ENGLAND-SI1:STERLING:IRELAND/EG1:EURO:GERMANY","2002","RootGrid",FALSE,TRUE)

Result: Parse Error

---

FXLDataPeriod Examples

The examples below are based on the following root grid sheet.

Minimum period (single value)

=SDMXPeriod("RootGrid","MIN")

Result: 2001 Why: Returns the earliest (minimum) SDMX period available on the root grid sheet.