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

office-comment message

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

Subject: Extending LOOKUP() Functions

• To: office-comment@lists.oasis-open.org
• Date: Thu, 31 Jul 2008 23:41:43 +0300

Dear list members,

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

RATIONALE
==========
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) }

SOLUTION
=========
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
=> 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.

Sincerely,