The difficulty is that A1 is numeric and Left (A1) is text
you can change your equation to
=lookup(value(left(A1,3)),A1:B4)
[note I had to change ";" to "," and do not know why you have ";2" at the
end of your equation. It does not work for me]

"khers Field" wrote:

I have a table of values like this from A1 to B4
A B
1 123 wall
2 254 chair
3 389 book
4 478 pen

Now I want to write a function (maybe with "lookup") to look at the
first tree characters in coloum A and return the value of it (here,
writen in coloumb B) I have tried with this:

A B C
1 123 wall =lookup(left(A1;3);A1:B4;2)
2 254 chair
3 389 book
4 478 pen

but I get #N/A in C1
if I write =lookup(123;A1:B4;2) I get 2
where as I would like to get wall

can anyone help pls?