Re: [office-comment] Extending LOOKUP() Functions

[Eike Rathke <erack@sun.com>]

Hi Leonard,

On Thursday, 2008-07-31 23:41:43 +0300, Leonard Mada wrote:

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

This is not specific to any [HV]LOOKUP function and already covered. See
section
3.2 Non-Scalar Evaluation (aka 'Array expressions')
subsection
2.2) Calculations with non-scalar inputs are a generalization of (2.1).
| When evaluating a formula in 'matrix' mode, and a non-scalar value is
| passed to a function argument that expects a scalar, the function is
| evaluated multiple times, iterating over the non-scalar input(s) and
| putting the function result into a matrix at the position corresponding
| to the input.

Following your example above, the matrix/array formula would be

{= VLOOKUP(A1:A3; 'my_range'; 2; FALSE)}

entered over an array of 3 rows. Which btw is already implemented in
OpenOffice.org

  Eike

-- 
 OpenOffice.org / StarOffice Calc core developer and i18n transpositionizer.
 SunSign   0x87F8D412 : 2F58 5236 DB02 F335 8304  7D6C 65C9 F9B5 87F8 D412
 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS

PGP signature