SAP Knowledge Base Article - Public

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

Symptom

* 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.

Cause

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.

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 , Dashboards and Presentation Design , Bug Filed

Product

SAP BusinessObjects Dashboards 4.5