See this example: Vlookup example.ods
If you have a VLOOKUP for a cell (eg =VLOOKUP(A1,C6:D8,2) ) the behaviour is expected.
However, if you do =VLOOKUP(LEFT(A2,3),C6:D8,2) and LEFT(A2,3) is the exact same as A1 (4.1 in the example document, VLOOKUP will yield #N/A.
How can you make VLOOKUP work with the LEFT operator?
In A1 there is a number and when you use LEFT() it’s converted in text, the list for search has numbers. Using VALUE(LEFT()) works.
Calc is very restrictive about the interpretation of text as numbers, It only works with direct references and VLOOKUP() searches in a list.
[ Apart the right answer given by @mariosv ]
To avoid unpredictable wrong results, don’t use VLOOKUP without explizit 4th Argument 0 if the Search-array ( in Case your Example C6:C8 ) is not sorted in ascending order.
The Formula in your Example should be:
=VLOOKUP(A1,C6:D8,2,0)
or
=VLOOKUP(VALUE(LEFT(A2,3)),C6:D8,2,0)
or you should sort C6:D8 on Column C