# Excel Lookup adds double quotes to string

I'm using this formula to get value form a table

``````=LOOKUP(C11;Alias[A];Alias[B])
``````

where C11 = 12354567

But when I use

``````=LOOKUP(LEFT(C11,7);Alias[A];Alias[B])
``````

where C11 = 12345678, the formula does not work.

Evaluating formula I have found out that `LEFT` adds double quotes making the formula like this:

``````=LOOKUP("1234567";Alias[A];Alias[B])
``````

How to approach that problem ?

-

You need to convert the text back to a number. You can do that by multiplying by 1 or adding 0 (or subtracting 0, or dividing by 1, well any arithmetic operation that doesn't change the final value):

``````=LOOKUP(LEFT(C11,7)*1;Alias[A];Alias[B])

=LOOKUP(LEFT(C11,7)+0;Alias[A];Alias[B])
``````

Text operations (like `LEFT`, `MID`, `RIGHT`, etc) return text values and not numeric ones.

A more 'formal' way would be to use `VALUE`:

``````=LOOKUP(VALUE(LEFT(C11,7));Alias[A];Alias[B])
``````
-

`Left` is returning a string, which I assume is being compared to numbers. This will fail. You need to change it to a number with something like `Int(Left(C11))` or `Value(Left(C11))`.

`=LOOKUP(INT(LEFT(C11,7));Alias[A];Alias[B])`

or

`=LOOKUP(VALUE(LEFT(C11,7));Alias[A];Alias[B])`

-
+1 for value, but I would avoid `int`, since in that case, it works, but should the text be different (e.g. `int(left(2.345,3))` would return 2 instead of 2.3. – Jerry Apr 5 '14 at 13:27
@Jerry I agree, I should have mentioned only use `int` with integers. Sometimes I forget the simple things. – Raystafarian Apr 5 '14 at 13:29