OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Extending LOOKUP() Functions

Dear list members,

I propose to extend the HLOOKUP(), VLOOKUP() and LOOKUP() functions 
[shortly LOOKUP() functions] as full array functions.

The LOOKUP() functions are usually used to search for more than just one 
value inside a different range. Users will most often search for 
multiple data, not just a single value, e.g.
  = VLOOKUP(A1; 'my_range'; 2; FALSE)
  = VLOOKUP(A2; 'my_range'; 2; FALSE)
  = VLOOKUP(A3; 'my_range'; 2; FALSE)

Instead of repeating the formula numerous times, it makes sense to use 
this formula just once and apply it as an array formula:
  {= VLOOKUP( '_array_to_be_searched_' ; 'my_range'; 2; FALSE) }

The LOOKUP() functions should be extended to cover array functionality:

  {= VLOOKUP(array '_array_to_be_searched_' ; 'my_range'; column ; 
logical TRUE/FALSE) }
  {= HLOOKUP(array '_array_to_be_searched_' ; 'my_range';     row  ; 
logical TRUE/FALSE) }
  {=  LOOKUP(array '_array_to_be_searched_' ; 'my_range' [; ForceArray | 
Array ] ) }


1.) formula used once instead of repeated multiple times
  => more structured spreadsheet
  => reduced error rate
  => avoids hard to trace errors when
        moving cells around / deleting rows / columns
      ==> a single formula needs at most to be updated

2.) more efficient computation
  => NO need for complex caching mechanisms
       to speed computation
    ==> range is evaluated just once
    ==> search can be made highly efficient
  => a single formula improves recalculations
    ==> will. trigger recalculations just once,
            after it has completed all computations

I hope that spreadsheets move more aggressively towards powerful 
vectorization capabilities and this is just one such step.


Leonard Mada

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]