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.

Example Spreadsheet Download and extract from the zip file to obtain an Excel version of the spreadsheet.

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.