VLOOKUP is, in its concepts, a bit outdated as I feel. You get more flexibility using a combination of MATCH with INDEX (or, even better, with OFFSET). You find a kind of demo here: ask45925VlookupLimitationsValueOfInexactMatches001.ods (Sorry! The upload feature is out of duty, obviously.) (Editing: Fixed meanwhile.)
==== Insertion 2015-02-09 ====
@Wildcard quoted :
Would you mind giving a comparative example of how they could be used together (or apart, whatever) to accomplish the same thing as a given VLOOKUP instance?
Didn’t you appreciate the examples in I17:T43 of the above linked in sheet?
==== End of insertion ====
However, the facility to allow for “inexact” matches is often valuable, and MATCH offers it in an even extended version. VLOOKUP only offers the “ascendibg” and the “exact” mode. To use the “ascending” mode you have to assure that the lookup values ared in ascending order. VLOOKUP will then to look for the greatest value not greater than the value to match in the lookup column (or the last one if no greater one can stop the search and EndOfRange must do it).
This is the specification in force for VLOOKUP (font formats only partly rebuilt):
6.14.12 VLOOKUP
Summary: Look for a matching value in the first column of the given table, and return the value of the indicated column.
Syntax: VLOOKUP(Any Lookup ; Reference|Array DataSource ; Integer Column [ ; Logical RangeLookup = TRUE() ] )
Returns: Any
Constraints: Column >= 1; The searched portion of DataSource shall not include Logical values. Evaluators may evaluate expressions that do not meet the constraint that the searched portion of a DataSource not include Logical values.
Semantics: If RangeLookup is omitted or TRUE or not 0, the first column of DataSource is assumed to be sorted in ascending order, with smaller Numbers before larger ones, smaller Text values before larger ones (e.g., “A” before “B”, and “B” before “BA”), and False before True. If the types are mixed, Numbers are sorted before Text, and Text before Logicals; evaluators without a separate Logical type may include a Logical as a Number. The lookup will try to match an entry of value Lookup . From a sequence of identical values <= Lookup the last entry is taken. If none is found the largest entry less than Lookup is taken as a match. If there is no data less than or equal to Lookup, the #N/A Error is returned. If Lookup is of type Text and the value found is of type Number, the #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. Each value in the first column of DataSource is examined in order (starting at the top) until its value matches Lookup. If no value matches, the #N/A Error is returned.
Both methods, if there is a match, return the corresponding value in column Column, relative to the DataSource, where the leftmost column in DataSource is 1.
The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. 3.4