need help with lookup
 

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?

Hi
Try using VLOOKUP()
=VLOOKUP(123,A1:B4,2,FALSE)

--
Andy.


"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

can anyone help pls?

Hi Khers

There are 2 ways 2 do this.

a) Insert a column before B and give the formulae as =left(a1,3) and then
use lookup or vlookup.
b) If it is not a very big sheet then use vlookup instead of lookup. the
formulae shall be =VLOOKUP(123,range,2,0)

These methods shall work as per your requirement.

Regards,
Raj

"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?

On 28.04.2005 09:54, Andy B wrote:
Hi
Try using VLOOKUP()
=VLOOKUP(123,A1:B4,2,FALSE)

it says the formula contains an error

Hi
When you say 'it says the formula contains an error', what is Excel actually
returning? Does it say #N/A, or #NAME or what?
The formula looks OK to me. It might be that your '123' is actually text,
rather than a number, Try:
=VLOOKUP("123",A1:B4,2,FALSE)

--
Andy.


"khers Field" wrote in message
...
On 28.04.2005 09:54, Andy B wrote:
Hi
Try using VLOOKUP()
=VLOOKUP(123,A1:B4,2,FALSE)

it says the formula contains an error

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?