5

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?

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
2

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.

EDIT:

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.

Improve this answer
4
  • 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
    Commented Apr 25, 2012 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
    Commented Apr 25, 2012 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
    Commented Apr 25, 2012 at 8:34
  • I found the problem. It was the last parameter (SortOrder). See my answer.
    – Ikke
    Commented May 1, 2012 at 8:20
0

Try

=IF(ISERROR(VLOOKUP(R2, Blad1.A14:I115, 9)), "", VLOOKUP(R2, Blad1.A14:I115, 9))
Improve this answer

You must log in to answer this question.

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