[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: HLOOKUP and VLOOKUP RangeLookup parameter
Hi, 6.13.4 HLOOKUP says | TBD: What should be done if it's not found. Current spreadsheets return | “something”, but we should probably at least ALLOW an Error to be | returned in such a case. Same for VLOOKUP. Actually what is returned is not "something" but depends on the optional 4th RangeLookup parameter, both in HLOOKUP and VLOOKUP: - If RangeLookup is omitted or TRUE or not 0, DataSource is assumed to be sorted ascending. The lookup will try to match an entry of value Lookup, if more than one are available any of them may match (due to binary search algorithms). If none is found the largest entry less than Lookup is taken and the corresponding value returned. If there is no data <= Lookup, a #N/A error is returned. If DataSource is not sorted, the result is undetermined and implementation-dependent. In most cases it will be arbitrary and just plain wrong due to binary search algorithms. - If RangeLookup is FALSE or 0, DataSource does not need to be sorted and an exact match is searched. The first match encountered is returned. If not found, a #N/A error is returned. Eike -- Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets. --Robert Weir on the OpenDocument formula subcommittee's list.