Why doesn't this formula work!?!
=LOOKUP(LEFT(A1,1),NUMS,LETS)
Column A contains numbers with multiple digits.
NUMS is a list of single digit numbers (Column A in Sheet 2)
LETS is a list of letters (Column B in Sheet 2)
I want to find the first digit of the number in A1, look it up in NUMS, and
return the corresponding value from LETS.
This works just fine as =LOOKUP(A1,NUMS,LETS) when A1 refers to a single
digit number from the NUMS list.
It also works just fine as =LOOKUP(1,NUMS,LETS) and returns the letter that
corresponds to the number 1.
It returns #N/A when my lookup value uses the left formula.
Help? Why doesn't this work? And is there another way to make this happen?
Thanks!
mikebres - 21 May 2008 23:39 GMT
The left function returns a text value so your comparison is trying to
compare text vs a number. So you don't get a match.
Stick a value function in the formula
=LOOKUP(VALUE(LEFT(A2,1)),Num,Let)
Mike
> Why doesn't this formula work!?!
>
[quoted text clipped - 16 lines]
>
> Thanks!
T. Valko - 22 May 2008 02:50 GMT
Another way:
=LOOKUP(--LEFT(A2),Num,Let)

Signature
Biff
Microsoft Excel MVP
> The left function returns a text value so your comparison is trying to
> compare text vs a number. So you don't get a match.
[quoted text clipped - 28 lines]
>>
>> Thanks!
T. Valko - 22 May 2008 03:01 GMT
Just to FYI...
None of the formulas suggested will work if the number in An is negative.
However, based on your description and the formula you posted I had already
assumed there would be no negative numbers.

Signature
Biff
Microsoft Excel MVP
> Another way:
>
[quoted text clipped - 32 lines]
>>>
>>> Thanks!