Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Lookup Lookup | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |