HLOOKUP and VLOOKUP RangeLookup parameter

[Eike Rathke <erack@sun.com>]

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.