VLOOKUP strange behavior

[chiche2024]

Hello,
Sorry if I’m repeating the question but I really couldn’t find an answer to my problem here.
I use vloocup quite often but recently I have a problem with its behavior.

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 20; OS: Linux 6.5; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u1
Calc: threaded

I have the following issue:
column B has the formula =VLOOKUP(A2,KILPI_BASE,4,0) it works fine only for the first row
KILPI_BASE is named range in sheet named kilpi
The combination of INDEX and MATCH produces same result in the columns C, D.
Here is a Screenshot and the file.

IMPORT_WINTER_2023_2024_VO.ods (123.3 KB)
I have Reinstalled the LibreOffice and all preferences are default. I can’t find what is wrong. Please help!

[Villeroy]

The look up values do not exist in column A of the kilpi sheet.

[mikekaganski]

Check the length of values in your Sheet2.A:A cells. E.g., Sheet2.A3 contains

while kilpi.A2751 has simply UM0409KIBLKS.

[chiche2024]

It seems i have obtained some non-printable characters from the csv which is the source of the column A. Thanks for pointing them. But how did you manage to see them. They don’t appear when i open the csv with text reader.

[karolus]

To fix it select Column A in Sheet2 and do:
⇒edit⇒find&replace
find

[^0-9A-Z]

replace with nothing
[x]regular Expression
[x]selection only
⇒⇒replace all
IMPORT_WINTER_2023_2024_VO_fixed.ods (132.7 KB)

[mikekaganski]

@chiche2024

But how did you manage to see them.

Notepad++ (a Windows app) shows them, when showing formatting marks.

[Villeroy]

Choose the right encoding when importing csv.