Hallo Andreas, sorry for the late reply. I hunk may you are right. I am using multiple * HLOOKUP and VLOOKUP. Otherhands i have experiences in INDEX in combination with HLOOKUP and WLOOKUP. With i can mirroring an Matrix. So i can read left from criteria. But never i recognized that these constructs cause such trouble. Thank you very much for your kindly service Lutz Andreas Saeger geschrieben am 18.2.10 12:01
>Lutz Dietrich (public) wrote: >> Am 17.02.2010, 21:17 Uhr, >schrieb Andreas Saeger ><[email protected]>: >> >> Hallo, thanks for the fast >response. >> But how to prevent >formattings an inefficient >lookups? >> > >This performs a lookup twice >for one result: >F(ISNA(VLOOKUP(A1;range; >1;0));"";VLOOKUP(A1;range >;1;0)) > >This performs the lookup in >the first column of "range" 3 >times: >X1: LOOKUP(A1;range;1;0) >Y1: LOOKUP(A1;range;2;0) >Z1: LOOKUP(A1;range;3;0) > >Lookup once and return >many values at once: >X1: ATCH(A1;vector;0) >Y1:AA1: INDEX(range;X1;0) >[Ctrl+Shift+Enter] > >MATCH works pretty fast >with many thousands of >values in ordered mode >(last argument 1 or missing): >X1: ATCH(A1;vector;1) >Y1: ¡NDEX(vector;X1) [test >for exact match] >Z1:AB1: >F(Y1;INDEX(range;X1;0);NA( >)) [Ctrl+Shift+Enter] > >Excel seems to maintain >some internal cache for >redundant calculations. > If I recall correctly, >information about the >calculation cascade is >part of the very complicated >Excel file formats. >ODF does not keep anything >like that, which makes it >easy to implement >but rather inefficient. > >Use a relational database >with millions of interrelated >records and >never look back to >spreadsheets. > > >----------------------------- >----------------------------- >----------- >To unsubscribe, e-mail: >discuss- >[email protected] >For additional commands, e- >mail: discuss- >[email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
