Previous in Forum: Laptops for Mechanical Engineers Next in Forum: MS-DOS Commands
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832

# Help with Excel

12/21/2009 1:22 AM

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.

Kind regards ……

__________________
"Just when I had all the answers, they changed all the questions"
Pathfinder Tags: Excel Limits Lookup
Interested in this topic? By joining CR4 you can "subscribe" to

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Power-User

Join Date: Jan 2007
Location: UK S.Northants
Posts: 485
#1

### Re: Help with Excel

12/21/2009 4:14 AM

Something that has caused all sorts of problems for me is working with other people's spreadsheets. If they have formatted the cells to be different, say text instead of general, I can't get rid of the formatting. In such cases I have opened a new workbook (in the same Excel window or you get values only) and copied the formulae across.

For whatever reason, I can never reformat the cells.

The only other issue that gets me sometimes is that the automatic recalculate function might have been turned off, F9 will do this.

Good luck.

Power-User

Join Date: Aug 2007
Location: Toronto
Posts: 239
#2

### Re: Help with Excel

12/22/2009 1:59 AM

So, do you have any hair left? These kinds of problems make me crazy also.

As you noted, there is no limit on the number of rows that Lookup can scan, so the problem is something else.

Okay, some obvious questions:

- is the whole column formatted the same way ?

- are there any blank rows (shouldn't matter, but who knows) ?

- if they need to be sorted, are they ?

- did you copy/paste this column from some other sheet? I've had major problems with importing columns that insist they're text, but don't act like text. I've had to copy/paste them into other columns in the same sheet in order to be recognized as text. Same problems with numbers.

Good luck

Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
#3

### Re: Help with Excel

12/22/2009 2:32 AM

HUX and Graebeard, I appreciate your suggestions and comments, but so far, still I am stumbling along with no success ... day by day, I'll keep trying :-) Kind regards ......

__________________
"Just when I had all the answers, they changed all the questions"
Associate

Join Date: Jun 2008
Posts: 34
#4

### Re: Help with Excel

12/22/2009 8:48 AM

Any way you can upload a demo spreadsheet (If u can't upload on this forum; upload on google docs or something and post hte link)? I just don't have skills to understand excel in words :(

Associate

Join Date: Jun 2008
Posts: 34
#5

### Re: Help with Excel

12/22/2009 8:51 AM

I have no idea how ur data is organized; can you change lookup range from \$D\$2:\$D\$500 to \$D\$2:\$D\$1000 and see if that makes any difference. As I said in my previous post, its easier to follow the formula in the spreadsheet than via words. So, I might be completely off here.

Associate

Join Date: Apr 2008
Posts: 32
#6

### Re: Help with Excel

12/22/2009 8:53 AM

Maybe the best and simpler thing is to use macro... if you are able to program you can do everything you want.

Associate

Join Date: Jul 2008
Location: Norfolk, VA
Posts: 50
#7

### Re: Help with Excel

12/22/2009 9:05 AM

Your post peaked my interest, because I normally use VLOOKUP for all my lookup needs, so I thought I'd look into this a little bit.

Two questions that might shed some light - what if you replace your formula with:

=IF(D2="","",IF(match(D2,'Summary Forecasts'!\$D\$2:\$D\$500)>0,D2,""))

(I think this accomplishes the intent)

OR with

=IF(D2="","",vlookup(d2,'Summary Forecasts'!\$D\$2:\$D\$500,1,false))

Anonymous Poster
#8

### Re: Help with Excel

12/22/2009 9:06 AM

I've had trouble in the past with excel lookups if the search column is not sorted in ascending order.

Associate

Join Date: Jul 2008
Location: Norfolk, VA
Posts: 50
#9

### Re: Help with Excel

12/22/2009 9:11 AM

Yep - excel help shows this as a requirement for LOOKUP. This is why I usually use VLOOKUP, which does not require the search array to be sorted in any way.

Active Contributor

Join Date: Mar 2009
Posts: 21
#10

### Re: Help with Excel

12/22/2009 9:14 AM

1) Is the searched data sorted in ascending order?

2) Since the data appears to be text, are there miscellaneous spaces/white space in some of the data? When text sorting, white space counts too. Thus "9-1" is not the same as " 9-1", "9 -1", "9 - 1", "9-1 ", etc. Using the LEN function to return string lengths might help identify if any text has unintentional white space.

3) "If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value".

Is the looked for value in the array that you're searching? Again, whitespace could mess with this in that the searched for value, and the target value, are different by some whitespace.

4) Try the function "VLOOKUP" instead of "LOOKUP", if available. Perhaps better success with the replacement for "LOOKUP". VLOOKUP is for vertical arrays of data, HLOOKUP for horizontal arrays.

Power-User

Join Date: Oct 2009
Location: Istanbul. European side
Posts: 151
#11

### Re: Help with Excel

12/22/2009 10:10 AM

Try using names instead of ranges.

Such as use the name "range1" instead of writing "\$D\$2:\$D\$500"

Participant

Join Date: Dec 2009
Posts: 4
#12

### Re: Help with Excel

12/22/2009 12:33 PM

Be sure that D2 corresponds to the right one, as you wrote it, it is referred in each spreadsheet. Write its spreadsheet name previously, like 'Project Descriptions'!D2 or whatever. If not, be sure the format is not white (sorry, I'm sure you checked it). If not, try to change the general format to other than txt. If not, you can allways change the way to find any value by doing it through coincidence and index...as some years ago. I have done some search in excel, I don't find problems with the order of magnitude. Good luck

Power-User

Join Date: Jun 2007
Location: Windsor UK
Posts: 103
#13

### Re: Help with Excel

12/22/2009 12:42 PM

This sounds like a formatting issue to me. Ensure that your data is in ascending order and perhaps try removing the hyphen. You may want to try using the =INDEX() function and see if that works

Good luck

__________________
Those who believe in telekinetics, raise my hand - Kurt Vonnegut
Member

Join Date: Dec 2009
Posts: 8
#14

### Re: Help with Excel

12/22/2009 1:55 PM

The Lookup function you are trying to use is sensitive to the order in which the lookup column has been sorted. Ideally, it mjst have been sorted in ascending or descending order, but the lookup column should not be in just any random order.

That said, I would suggest instead using the 'Vlookup' function for the kind of task you have. Use the proper four-argument context for the function and select 'False' for the last argument, which will then NOT require the file be sorted. In all cases, however the lookup column must then contain 'unique' values; i.e., they must not repeat. Note that there is a companion 'Hlookup' function for when your data is organized 'sideways' (i.e., horizontally instead of vertically).

Lastly, be sure that the Lookup values (which must always be in the left-most column of the array you define) has no 'confederate' characters embedded in the data values. What I mean here is that '9085-06' is not the same as '9085-06 ' (which has an extra blank space at the end). This is a common trip-up point that is hugely frustrating until discovered. You can use the 'trim' function to 'cleanse' search column data of unwanted characters like that and then re-copy the cleaned-up data over the original using the popular 'copy-paste-special-values' over the top of the originals.

Associate

Join Date: Jul 2008
Location: Norfolk, VA
Posts: 50
#15

### Re: Help with Excel

12/22/2009 1:59 PM

A bunch of people have mentioned that you can't have spaces in your data - good point.

A quick way to get rid of them is find/replace: Find " " Replace "". Just make sure you don't include cells where you want to keep spaces...

Associate

Join Date: Sep 2009
Posts: 27
#16

### Re: Help with Excel

12/22/2009 2:49 PM

Hi,

Modify the formula as

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

Regards

Baiju

Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
#17

### Re: Help with Excel

12/22/2009 9:24 PM

To all my friends and colleagues ... YOU DID IT

Using VLOOKUP did the trick ... although I am still baffled by why LOOKUP didn't work (I checked all the sorting, and hidden " ", and found no errors), but that really doesn't matter. The important part is that your solutions helped me greatly.

Thanks to all, and HAPPY HOLIDAYS, whatever, however and where ever you choose to celebrate.

Kindest regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Power-User

Join Date: Jan 2007
Location: UK S.Northants
Posts: 485
#19

### Re: Help with Excel

12/23/2009 7:57 AM

After reading this I realized all of my sheets use either VLOOKUP or HLOOKUP, I hadn't appreciated the difference. It's always important to post the resolution / solution on these threads. It gives us closure. It always bugs me when a thread is left open, so thanks for that. I will watch out for this in future. So thanks for that.

Associate

Join Date: Jun 2008
Location: Gujarat
Posts: 43
#18

### Re: Help with Excel

12/22/2009 11:43 PM

Hi ,

There is no range for lookups in Excel . you can lookup any values from any spreadsheeet till the last coulmn of the Spread sheet.

If you are using Excel 2007 juz select the column you want to lookup from any spread sheet . Go to formula tab and define a name for the column .It will automatically take the values from (A1 :Axxxxxxxxx) .similarly define the names for different colums . It's a short cut .start Vlookup function .Instead of specifying a range u can only insert the name and it will work . for example =vlookup(ear, dia:value , x) represents the index no . Ive tried it too. It works.

Regards

Earnest.

Interested in this topic? By joining CR4 you can "subscribe" to