Calc/Performance/VLOOKUP
From Apache OpenOffice Wiki
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) | =VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 3) | =VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 4) |
| 2 | key2 | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 2) | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 3) | =VLOOKUP( $A2; $Sheet2.$A$1:$D$12345; 4) |
| 3 | key3 | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 2) | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 3) | =VLOOKUP( $A3; $Sheet2.$A$1:$D$12345; 4) |
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)); ""; VLOOKUP( $A1; $Sheet2.$A$1:$D$12345; 2))
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.
