Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question
up vote 3 down vote accepted

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])
share|improve this answer

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])

share|improve this answer
1  
+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
1  
@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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .