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