Calc/Performance/VLOOKUP
A caching mechanism was implemented for OOo2.4, for details see issue mentioned below.
The VLOOKUP spreadsheet function is often used to do various lookups for identical key criteria and display different fields of the same result record in one row. Usage is something similar to
| A | B | C | D | |
|---|---|---|---|---|
| 1 | key1 | =VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 2; 0) | =VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 3; 0) | =VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 4; 0) |
| 2 | key2 | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 2; 0) | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 3; 0) | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 4; 0) |
| 3 | key3 | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 2; 0) | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 3; 0) | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 4; 0) |
Currently, for each VLOOKUP executed the lookup in the $Sheet2.$A$1:$D$12345 data array is done again. The situation even worses when the formula is something like IF( ISERROR( VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 2; 0)); ""; VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 2; 0))
Note that the last parameter to VLOOKUP is 0 for a non-range unsorted exact match as the worst case, where no binary search is possible to do the lookup.
A caching strategy could be introduced that remembers the record being looked up to fetch subsequent fields to be displayed in the same row without doing the lookup again. In large spreadsheet documents this would speed up calculation significantly. There is Issue 81336 (FIXED CLOSED) for this, with a test case document attached.
