Extending LOOKUP() Functions

[Leonard Mada <discoleo@gmx.net>]

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 ] ) }


ADVANTAGE
===========

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.

Sincerely,

Leonard Mada