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"
I have also tried with =VLOOKUP(123,A1:B4,2,FALSE) it gives me "there is
an error in the formula"


can anyone help pls?

in the VLOOKUP variant, replace commas with semicolons (that is, if you have
European settings, as your lookup version suggests)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"khers Field" wrote in message
...
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"
I have also tried with =VLOOKUP(123,A1:B4,2,FALSE) it gives me "there is
an error in the formula"


can anyone help pls?

=vlookup(left(a1;3),a1:b4;2;false)
if the values in column A are really numbers:
=vlookup(--left(a1;3),a1:b4;2;false)

=left() returns a string.
the -- converts it to a negative number, then to positive.

Debra Dalgleish has a nice explanation at:
http://www.contextures.com/xlFunctions02.html

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"
I have also tried with =VLOOKUP(123,A1:B4,2,FALSE) it gives me "there is
an error in the formula"

can anyone help pls?

--

Dave Peterson