Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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 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 at 13:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.