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!

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

"Ab" wrote:

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!

Another way:

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


--
Biff
Microsoft Excel MVP


"mikebres" wrote in message
...
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

"Ab" wrote:

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!

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Another way:

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


--
Biff
Microsoft Excel MVP


"mikebres" wrote in message
...
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

"Ab" wrote:

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!