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

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

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.

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