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

#### sleepdoc

##### New Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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

Replies
0
Views
128
Replies
7
Views
152
Replies
5
Views
319
Replies
3
Views
244
Replies
0
Views
262

1,203,761
Messages
6,057,215
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back