Hi to all,

I have a very odd problem in Excel that is making me crazy … no matter how I try to solve it, I can't seem to find the answer. I will try to describe it in detail …

Here is an example of a section of the spreadsheet … for reference, let's label the columns D, E, F, G, and H.

In column D, is the reference data … in particular, these are our format of project numbers. I use this to do a simple "LOOKUP" in other columns. The problem is, it stops at 250 rows. Of course, I am looking up other relevant data, but when it failed, I just set up some simple tests to see if the problem was the data, or some other issue.

In column G I'm looking for data from the other spreadsheet (same workbook) … the formula is simply …

=IF(D2="","",LOOKUP(D2,'Summary Forecasts'!$D$2:$D$500))

What you are seeing here is a returned value up to 250 rows, then nothing.

As a test, I am doing the same thing from another spreadsheet (still the same workbook) in column H with a similar formula …

=IF(D2="","",LOOKUP(D2,'Project Descriptions'!A$2:A$1000))

And you can see there is no issue, even from a much larger array, in finding everything.

SO, I did another test to see if the data was somehow corrupt. In this particular case, the data in both this spreadsheet and the sheet 'Summary Forecasts' is the same (it won't always be, which is why I need to do a LOOKUP instead of just 'look here'. Column E just asks …

='Summary Forecasts'!D2

And in column F, I ask if the information is the same or not …

=IF(D2<>E2,"ERROR","")

In no case is the information reported as being different … in no case is there an ERROR reported.

I have searched to see if there is a LIMIT on the number of cells I can check, but I find nothing useful, and, quite frankly, this particular case is TINY compared with others I have done.

FYI, I am working in Excel 2007, but safe as 2003 (too many folks still use 2003) … I have tried saving as 2007, and working in the native format, but that didn't help …

I am really hoping someone can find some stupid mistake I am making … I've never had this issue before … it's making me crazy.

Thanks in advance for your advice.

Kind regards ……

## Comments rated to be "almost" Good Answers:

Re: Help with Excel" by DCaD on 12/22/2009 9:24 PM (score 1)Re: Help with Excel" by jgjengr on 12/22/2009 9:05 AM (score 1)Re: Help with Excel" by Graebeard on 12/22/2009 1:59 AM (score 1)