Help with Excel

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.

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

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 ......

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 :(

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.

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

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))

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

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.

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.

Try using names instead of ranges.

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

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

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

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.

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...

Hi,

Modify the formula as

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

Regards

Baiju

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 ...

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.

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.