LibreOffice Calc Problem for VLOOKup

[irfansyah]

I am a regular excel user, when I switch to libreoffice calc I found this simple usage are annoying, anybody knows how to do a proper VLOOKUP or is this a bug or something ?

let say
you have A…Z,[space],0…9 on Cell B4:B41 and its value from 1…37 on C4:C41
and I make a simple VLOOKUP with formula something like this
=VLOOKUP(E4,$B$4:$C$41,2) for cell E5
and I copy the rest

from the screenshot as you can see from T to the rest you will have #N/A (i found this on windows) and on Linux the result would be the last values which is 37
so anybody can show me the proper way to use VLOOKUP in this
I have already try using Only Office etc the same problem exists but not with Excel or Google Sheet off course,

So I believe this might be a bug or something

regards

[Villeroy]

Vlookup is not working fails with N/A return despite i have value

LOOKUP, MATCH and V/HLOOKUP are typical spreadsheet functions. However, most people try to mis-use a spreadsheet program as a database. 1 A 3 B 5 C 7 D =LOOKUP( 4 ; A1:B4) => B although 4 does not occur in the search column. Same result with equivalent VLOOKUP( 4 ; A1:B4 ; 2) or VLOOKUP( 4 ; A1:B4 ; 2 ; 1) or INDEX(B1:B4 ; MATCH( 4 ; A1:A4)). Spreadsheet lookups scan an ascending numeric scale and return some corresponding value that falls into a certain range of that scale. The functions…

[mikekaganski]

No this is not a bug. This is perfectly normal result on VLOOKUP used incorrectly - both in Calc, and in Excel. See “Problem: The lookup column is not sorted in the ascending order” in VLOOKUP troubleshooting documentation from MS.

Your data in B4:C41 is not sorted. This means that your VLOOKUP must be used with the fourth argument set to FALSE (0). When you use VLOOKUP in the default “sorted range lookup” mode, it will return you some random result, that depends on a chance and the details of the internal implementation, but is just random.

It may happen that it returns a wanted result in this wrong-use case, but that’s the most dangerous thing - it may give you a wrong impression that you used it correctly, and then it would hit you when you don’t expect that. Try looking for the space (that should give you 27) in Excel.

=VLOOKUP(E4;$B$4:$C$41;2;0)

VLOOKUP_1_vs_0.ods (11.1 KB)