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.

---

Maximum period (single value)

=SDMXPeriod("RootGrid","MAX")

Result: 2003 Why: Returns the latest (maximum) SDMX period available on the root grid sheet.

---

Range (min and max — two‑cell spill)

=SDMXPeriod("RootGrid")

Result (two cells): 2001, 2003 Why: Returns both the MIN and MAX SDMX periods from the root grid sheet.

---

Error: sheet not found

=SDMXPeriod("NonExistantRootGrid",,TRUE)

Result: Sheet Not Found Why: The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.

---

Troubleshooting

MIN period isn’t the earliest in the grid

Why: SDMXPeriod("…","MIN") returns the first period it finds in the grid header, which is typically (but not guaranteed to be) the earliest.

Fix: Sort your period headers left→right so the earliest period is first.

---

MAX period isn’t the latest in the grid

Why: SDMXPeriod("…","MAX") returns the last period it finds in the grid header, which is typically (but not guaranteed to be) the latest.

Fix: Sort your period headers left→right so the latest period is last.

---

FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode”

Why: Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.

Fix: Render the root grid with periods in the header row and values beneath them.

---

Grid not found

Why: The grid header (dimensions + period labels) must be on row 2 of the root sheet; otherwise the grid isn’t detected.

Fix: Move headers to row 2 on the root sheet.

---

Sheet not found

Why: The sheet name you passed doesn’t match an actual worksheet.

Fix: Use the exact sheet name that exists in the workbook.

---

Key not found

Why: The series key is misspelled or doesn’t exist in the root grid’s Series column. Fix: Copy–paste the key from the grid (avoid typos) and ensure the root grid includes full series keys.

---

Expected multiple cells, but only one cell returned

Why: Multi‑cell (“spilled”) results require a modern Excel (later than 2016) that supports dynamic arrays.

---

Invalid power

Why: The right‑hand side of ^ must be a numeric literal or a reference to a numeric cell; a series‑derived expression/key is invalid (see example using …^(EG1:EURO:GERMANY-26)).

Fix: Replace the exponent with a number (e.g., ^2) or a cell that contains a number.

---

Non‑numeric value in grid

Why: The target cell in the root grid holds text (e.g., g), so numeric calculation fails.

Fix: Correct the source cell to a number or point the formula to a numeric period.

---

Divide by zero

Why: Your denominator evaluates to 0 (e.g., EG1:EURO:GERMANY - 26 = 0).

Fix: Choose a non‑zero reference.

---

Parse error

Why: The items/expression string has a syntax error (operator typo, or missing parenthesis).

Fix: Check operators and parentheses; compare against a working example and re‑enter carefully.