SAP Knowledge Base Article - Public

1205133 - Replace Formula-based indexes for VLOOKUP array with INDEX / MATCH because indexes are not changing at run time


* Formula-based indexes for your VLOOKUP array are not changing at run time.
* If you have a VLOOKUP function that contains a formula in the index column, the index column cell will always contain the initial value of the formula.


In order to maintain the speed and efficiency of the VLOOKUP function, Crystal Xcelsius does not recalculate formulas in the index column of a VLOOKUP array.


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


The example we will use will be as follows
- 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


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.


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


SAP BusinessObjects Dashboards 4.5