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
Formulabased indexes VLOOKUP INDEX MATCH run time Crystal Xcelsius recalculate Excel Spreadsheet XML file , 809921 , KBA , BIRAXL , BusinessObjects Dashboards and Presentation Design , Bug Filed