How do we use lookup ?

[khers Field]

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?

 

[Niek Otten]

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

 

[Dave Peterson]

=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