vlookup is wrong when using "Data Sources" data [closed]

asked 2014-02-03 02:21:00 +0200

Stuzz gravatar image

updated 2016-02-19 06:40:10 +0200

Alex Kemp gravatar image

In Calc if you hit [F4] you open the "Data Sources" view which shows Base databases, queries etc. I run a query, copy all of its resulting data and paste into a Calc as:

#|  A    |  B
1|  ABC  |  123
2|  XYZ  |  890

I create a vlookup: =VLOOKUP("XYZ",A1:B2,2)

What I expect to happen is, vlookup will find "XYZ" on row2 and return the value of the data in row 2, column 2 (aka B), which should be "890". What actually happens is, vlookup finds "XYZ" on row 2, but returns data from row 1, column 2, which is "123".

I can 'fix' the result to be what I expect if I delete the heading of column B (and shift cells up, effectivley offsetting the data by 1 row). In my limited experience, vlookup works as expected in every other situation as long as the array data it is using has NOT come from a "Data Sources" source.

Does any of this ring true for anyone else?

Edit: Just to make it plainly clear what I'm seeing

image1:showing formula

Above is the data in columns A and B and the vlookup formula. Below is the result of the formula. The only difference between the 2 images is I toggled showing formulas with ctrl+~.

image2:showing result

If it's any help, creating the same query from the same data in MSQuery and using the same vlookup formula in Excel works as expected.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2016-02-19 06:39:30.350828


Hi @Stuzz, please what is your LibreOffice version and your Operating System?

m.a.riosv gravatar imagem.a.riosv ( 2014-02-03 02:28:45 +0200 )edit

Oh yes.. I knew I forgot something:

Win7 Pro SP1 32-bit

LibreOffice Version: Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71

(I recently upgraded from 4.1.x to see if this was fixed)

Stuzz gravatar imageStuzz ( 2014-02-03 02:57:24 +0200 )edit

I have just verified with Win7x64 LibreOffice, over a range from Bibliography and I can't reproduce the issue. Looking for text, except you are sure it is sorted, it is useful the fourth parameter with zero, to fix searched data as unsorted.

m.a.riosv gravatar imagem.a.riosv ( 2014-02-03 03:28:15 +0200 )edit

Okay, thanks @mariosv, that's a big help. I just tried with Bibliography myself and all looks fine. I also tried with another datasource, which was fine, and a different table from the issue-causing datasource and it also was fine. Not sure what to do the fix the problem, but at least I can look in the correct direction now.

edit: Actually I still have no idea what's going on and where the problem is. Will futz about some more.

Stuzz gravatar imageStuzz ( 2014-02-03 04:23:29 +0200 )edit