Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / May 2008

Tip: Looking for answers? Try searching our database.

Lookup references

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ab - 21 May 2008 23:17 GMT
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.