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: Re: [office-comment] Extending LOOKUP() Functions

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
3.2 Non-Scalar Evaluation (aka 'Array expressions')
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 / 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

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