[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. 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
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]