Lookup references

[Ab]

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]

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

 

[T. Valko]

Another way:

=LOOKUP(--LEFT(A2),Num,Let)

 

[T. Valko]

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.