Symptom
Cause
Resolution
To work around this designed limitation in Xcelsius, you can replace the VLOOKUP formula in the Excel Spreadsheet with a INDEX/MATCH Formula
Formula Replacement
------------------
VLOOKUP(<Lookup_Value>,<Table_Array>,<Col_Index_Number>, [range_Lookup])
Replace with
INDEX(<Table_Array>,MATCH(<Lookup_Value>,<Col_Index_Table_Array,0),2)
Example
------------------
The example we will use will be as follows
Cell
- A1 = orange
- A2 = 90
- B1 = yellow
- B2 = 80
- C1 = blue
- C2 = 100
|
a |
b |
-------------------------
1 | orange |
90 |
-------------------------
2 | yellow
| 80 |
-------------------------
3 |
blue | 100
|
Failing VLOOKUP Formula is as follows
=VLOOKUP("yellow", A$1$:B$10$,2,FALSE)
NOTE: With a VLOOKUP formula, if the cell range A1:B10 are being populated by an external XML file, and a row is added to the top of the cell range (all other rows are shifted down). The result of the VLOOKUP formula in Excel would still be 80 as expected but in an Xcelsius document would be 90 (the value that now exists in cell B2, the cell that the formula initially referenced)
Resolving formula
------------------
The formula need to work around this issue would be as follows
=INDEX(A$1$:B$10$,MATCH("yellow",A$1$:A$10$,0),2)
NOTE: If an XML file is being used to populate the data cell range (in our example A1:B10, 10 rows), the XML needs to contain at least 10 entries, regardless if the entries are empty.
Keywords
Formula-based indexes VLOOKUP INDEX MATCH run time Crystal Xcelsius recalculate Excel Spreadsheet XML file , 809921 , KBA , BI-RA-XL , Obsolete: BusinessObjects Dashboards and Presentation Design , Bug Filed