Why is the LOOKUP function failing to work sometimes ????

[sleepdoc]

Thanks in advance to anyone who can help me on this issue.

The LOOKUP function is pretty stright forward and easy to use. I use it often. However, I have a particular form that uses "LOOKUP" to fill itself up from a master data table. I am looking up sales data like customer name, social sec # etc. Nothing fancy.

But for some reason, the lookup function is failing to be recognized in certain cells. Rather than bringing back the looked-up answer, it simply displays the formula in those cells. In fact, sometimes in the cells that are "looking-up" just fine, if I make a manual edit (like change the cell to lookup from or whatever) it suddenly stops working and starts showing the formula as if it were just a text string.

Here is the exact cut and paste of one of the failing formulas....

=LOOKUP($L$1,'Master SALES LISTS Data'!$Y$12:$Y$811,'Master SALES LISTS Data'!BS$12:BS$811)

So I have some theories, but none have been useful.

First I thought maybe the fact that I had such along name with spaces in the sheet i was looking up from might be cuasing the issue. but it works in 85% of the other cells. so why failure in 15% of the cells?

I do not believe the issue relates to what can be seen in the formula itself. My formulas (I believe) are syntactically correct. At least i can say that identical syntax in neighboring cells are working without a hitch. I feel as though I may have "overwhelmed" excels ability to do multiple "lookups" on one sheet. Seems silly as I have 1 gig of ram on a modern computer and I am dealing with excel 2003 (most recent version).

ANy ideas anyone? HELPPPPPPPP

Thanks,

Glenn

 

[sleepdoc]

Problem Solved

The issue had to do with formatting of cells

For some reason, i waS ENTERING A "NUMBER" and looking it up against a column of numbers and then trying to post the result into a cell that wasformatted as text (the answer was in fact, text ... a persons name). Excel sems to allow this sometimes and not other times (no rhyme or reason yet, just success). Once I changed the final results cell to a General format, it started working.

Go figure.

: )

Glenn