FXLDataLookup Function - Revision history

Vmurrell: /* FXLDataPeriod Formula */

2026-06-15T13:35:30Z

‎FXLDataPeriod Formula

Vmurrell: /* FXLDataLookup Formulas */

2026-06-15T13:32:27Z

‎FXLDataLookup Formulas

Vmurrell at 13:26, 15 June 2026

2026-06-15T13:26:42Z

Vmurrell: Vmurrell moved page FXLDataLookup and FXLDataPeriod Functions to FXLDataLookup Function: split article into 2

2026-06-15T13:19:55Z

Vmurrell moved page FXLDataLookup and FXLDataPeriod Functions to FXLDataLookup Function: split article into 2

Vmurrell: /* Parse error */

2026-04-20T17:22:46Z

‎Parse error

Vmurrell: /* FXLDataLookup Examples */

2026-01-26T09:33:23Z

‎FXLDataLookup Examples

Vmurrell: /* FXLDataLookup Examples */

2026-01-25T10:33:43Z

‎FXLDataLookup Examples

Vmurrell: Vmurrell moved page FXLData - New Functions to FXLDataLookup and FXLDataPeriod Functions

2026-01-23T07:43:29Z

Vmurrell moved page FXLData - New Functions to FXLDataLookup and FXLDataPeriod Functions

Vmurrell at 07:40, 23 January 2026

2026-01-23T07:40:39Z

Vmurrell: /* FXLDataPeriod Examples */

2026-01-23T07:38:31Z

‎FXLDataPeriod Examples

@@ Line 60: / Line 60: @@
 ===== 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).
 ==== Syntax ====

@@ Line 14: / Line 14: @@
 FXLDataPeriod [[FXLDataPeriod_Function|please refer to this article.]]
-==== FXLDataLookup Formula ====
+=== 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
@@ Line 21: / Line 21: @@
 depending on how many items and periods you pass.
-===== Syntax =====
+==== Syntax ====
 FXLDataLookup(items, periods, sheetName, [autoRefresh], [errorMessages])
-===== Parameters =====
+==== Parameters ====
 '''items'''
@@ Line 42: / Line 42: @@
 * 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 =====
+==== Supported Operators - items parameter ====
 '''Arithmetic'''
 * + (add)
@@ Line 64: / Line 64: @@
 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 =====
+==== Syntax ====
 FXLDataPeriod(sheetName, [what], [errorMessages])
-===== Parameters =====
+==== Parameters ====
 '''sheetName''' - The worksheet containing the root grid with the expected header/layout
 '''what'''
@@ Line 267: / Line 267: @@
 ---------------------------------------------------------------------------------------------------------
 ---------------------------------------------------------------------------------------------------------
 === Troubleshooting ===
 ---------------------------------------------------------------------------------------------------------
-===== FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” =====
+===== FXLDataLookup behaves oddly in “vertical mode” =====
-'''Why''': Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.
+'''Why''': The function 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.

@@ Line 1: / Line 1: @@
 [[Category:Functions Reference FXLData]]
 Introduced in H1.3.0.03 (January 2026).
 == FXLDataLookup 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.
@@ Line 13: / Line 12: @@
 * 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).
+FXLDataPeriod [[FXLDataPeriod_Function|please refer to this article.]]
 ==== FXLDataLookup Formula ====

← Older revision		Revision as of 17:22, 20 April 2026
Line 379:		Line 379:

	'''Fix''': Check operators and parentheses; compare against a working example and re‑enter carefully.		'''Fix''': Check operators and parentheses; compare against a working example and re‑enter carefully.
		+
		+	===== Operations on multiple reference cells =====
		+	Require using ampersands and quotes.
		+	'''Why''': When performing operations like summing or multiplying without ampersands and quotes, Excel evaluates the referenced cells before passing the value to FXLDataLookup, which results in an incorrect input for the formula.
		+
		+	'''Fix''': Use ampersands and quotes, e.g. FXLDataLookup(E1&"+"&F1;D1;"דוגמאות";FALSE;TRUE)
		+
		+	This ensures the first parameter is passed as a string and correctly processed by the formula.

@@ Line 85: / Line 85: @@
 [[File:Example-spreadsheet.png|1000px]]
-[[:File:FXLDataLookupSamples-V1.zip | Example Spreadsheet]] Download and extract from the zip file to obtain an Excel version of the spreadsheet.
+[[:File:Example-Spreadsheet-V2.zip | Example Spreadsheet]] Download and extract from the zip file to obtain an Excel version of the spreadsheet.
 ==== Basics ====

← Older revision	Revision as of 13:19, 15 June 2026
(No difference)

@@ Line 84: / Line 84: @@
 [[File:Example-spreadsheet.png|1000px]]
 ==== Basics ====
@@ Line 265: / Line 267: @@
 ---------------------------------------------------------------------------------------------------------
 === FXLDataPeriod Examples ===
 The examples below are based on the following root grid sheet.

← Older revision	Revision as of 07:43, 23 January 2026
(No difference)

← Older revision		Revision as of 07:40, 23 January 2026
Line 1:		Line 1:
		+	[[Category:Functions Reference FXLData]]
	Introduced in H1.3.0.03 (January 2026).		Introduced in H1.3.0.03 (January 2026).

← Older revision		Revision as of 07:38, 23 January 2026
Line 299:		Line 299:
	'''Result''': Sheet Not Found		'''Result''': Sheet Not Found
	'''Why''': The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.		'''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.