Ask Your Question

Why isn't VLOOKUP working here?

asked 2015-02-08 09:56:49 +0100

Wildcard gravatar image

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.)

C:\fakepath\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.)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-02-08 20:55:13 +0100

karolus gravatar image

updated 2015-02-09 09:07:06 +0100

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

edit flag offensive delete link more

answered 2015-02-08 10:11:23 +0100

JohnSUN gravatar image

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

edit flag offensive delete link more


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?

Wildcard gravatar imageWildcard ( 2015-02-08 10:17:39 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2015-02-08 10:34:47 +0100 )edit

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! ;) You gave WHY it only worked sometimes, which was the mystery for me.

Wildcard gravatar imageWildcard ( 2015-02-09 03:54:38 +0100 )edit

answered 2015-02-08 11:34:00 +0100

Lupp gravatar image

updated 2015-02-09 19:09:14 +0100

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 ... (more)

edit flag offensive delete link more


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?

Wildcard gravatar imageWildcard ( 2015-02-09 03:56:23 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-02-08 09:56:49 +0100

Seen: 9,051 times

Last updated: Feb 09 '15