Why isn't VLOOKUP working here?

Okay, I was playing around with numbers in a Calc sheet and one of the formulas started doing something bizarre. It’s the same formula in A5, B5 and C5 (with different reference cells), but C5 isn’t working. Note that if you change C1 to “11”, none of A5:C5 will work. If you change C1 to “3”, A5:B5 will not work but C5 will.

I’m not trying to do something complicated with those formulas—just look up the number “1” in another column, then return the value of the cell right next to the number “1.”

What gives??? Why the errors and why only sometimes??? (P.S.: I tried “LOOKUP” also with the same results.)

modular math.ods

(For those interested in what the heck all the other formulas are, I’m playing around with the extended Euclidean algorithm and modular arithmetic equations.)

The reason is:
without fourth parameter or with 4th Parmeter 1 you’ll advice calc to take the search Array as sorted in ascending order - that allows an binary search algorithm instead of stupid comparing every entry until the the needle is found - but leads to unpredicable Returnvalues if the Search -array is not sorted in real.

the non-binary algorithm need for lists of 10^6 in avarage 500000 loops,
and in worst case 10^6 loops

binary search on the same list needs in worst case ~20 loops ( log2( 10^6 ) => ~20 )

wiki binary-search

VLOKUP() result depends on the order of values in a column of the search, if the fourth parameter of the function is omitted or specified TRUE. Just add a fourth function parameter 0


Well that WORKS…but it seems weird that without that parameter, it worked sometimes but not always. I couldn’t figure out any pattern, so I noted some different values for C1 that cause different cells to fail.

For now I’ll use the sorting parameter so I can continue working, but…? Can you see why it is working in A5 and B5 when that parameter is omitted?

No, unfortunately I can’t explain it. This is a mystery to me too. It can not to work properly … but it works!

Karolus, would you mind converting that comment to an answer? That IS the information I was looking for. (Although JohnSUN’s answer is very helpful and practical! :wink: You gave WHY it only worked sometimes, which was the mystery for me.

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

This is interesting. I’ve used MATCH and INDEX before, but it was a long time ago and I never conceptualized what they were for. 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?