Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table of data in a sheet (blad1.A2:I115). The first column (A) is a list of numbers that is being referenced. The last column (I) has the value I want to get returned.

This is what I tried:

=VLOOKUP(R2, Blad1.A14:I115, 9)

But it returns #N/A

R2 contains the number 1269, which should be found in blad1.A73.

Can anyone explain what I'm doing wrong?

share|improve this question
up vote 4 down vote accepted

Although not tested on libreoffice, because we have moved to Google Docs, experimenting there has me found that the sorted parameter defaults to true, while the first column isn't sorted.

Setting that to false fixes the problem.

share|improve this answer

Since the formula's structure looks ok, it may be a problem of Function separators. I don't have dutch l11n installed, so i can't test this myself. You may check the formula separators (; or ,) using Tools -> Options -> LibreOffice Calc -> Formula. This is the setting for en_us localization:

enter image description here

If this isn't the reason, you could try to create the formula from scratch using the formula wizard. Since the wizard displays interim results, it may be helpful to narrow down the problem.


The result #N/A is thrown if there's no exact match for the search criteria. So i assume it's not a matter of finding the result value in Column I but in finding a match for 1269 in Column A. A reason for this may be if the search criteria isn't a number but a string containing a number (the cell content would be '1269 instead of 1269 (notice the apostrophe). OpenOffice won't match the string 1269 with the number 1269.

share|improve this answer
Note that I built the formula using the wizzard, and it added , itself. Now I have changed the seperator to ; and I do get a result, but it seems to always pick the first row, no matter what the first argument is. – Ikke Apr 25 '12 at 8:18
Is my understanding of VLOOKUP right that it looks at the first column of the range you gave for the search criteria, and then returns the nth column from that row where n is the index? – Ikke Apr 25 '12 at 8:29
@Ikke: yes, you're right. It should use the left column of the datatable to lookup the search criteria. Notice that when counting the nth column, the leftmost column (search criteria) counts as first column, so if the datatable starts with Column A (search criteria), Column B is column 2. Maybe there are duplicates in the first column, so there may be more than one row that may qualifiy for the given search criteria? Other reasons for mismatching may be different data types (Integer values, strings?). – tohuwawohu Apr 25 '12 at 8:34
I found the problem. It was the last parameter (SortOrder). See my answer. – Ikke May 1 '12 at 8:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .