Sverweis Vlookup does not work properly

Question

My sverweis formula only works if I copy the search criteria data (lower part table 1) D17 to the matrix part ( table 2). I search for the numbers in column C of table 2. At rows 18 and 25 I did this and got the correct result(42 and 19) At rows 27 and 29 there are numbers in table 2 but I get only NV like in the other rows.

Formatted column D on table 1 and column A table 2 on text. I suspect a formating error but dont now the solution. Tried everything!

Hope someone has a solution
Many thanks

Jacqueline

Translated with www.DeepL.com/Translator

sergeibaklan · Answer

Hi Jacqueline,&nbsp;That's since you have extra spaces in names, try=VLOOKUP(TRIM(D17),Tabelle2!$A$2:$C$238,3,FALSE)and attached"Grüne Kontaktlinsen (Electro Green)&nbsp;" &lt;&gt; "Grüne Kontaktlinsen (Electro Green)"&nbsp;

jacqueline urbach · Answer

Hi Sergei Many thanks. It works with "trim" but only on those that I did not copy from table 1 to table two. It looks like ti works on those with spaces and those that have no spaces the original formula works. Can I combine the two versions? One with trim and the other without. What i don't understand is why it works when I copy past from tabkle 1 to table 2 even so the formating is exactly the same. Tried to upload the table but can't find a button Again thanks Regards Jacqueline