I have one sheet that has many columns and is basically a mess (but that's
another story).  The people using it update values in 2 of the columns, and
then HAND ENTER the same data into another worksheet in another workbook.
So I say, "that's ridiculous, there's gotta be a way to do this better."
However, a complicating factor is that columns are constantly being added in
the original worksheet.  OK, so I'll use absolute references, which still
update the column as it moves.  No good, though, because sometimes they
Data-->Sort the first worksheet also.

SO, I figure the Lookup function should work.  So in the second sheet, in a
particular cell, I put:

=LOOKUP("Text",'[WorkBookName.xls]Worksheetname'!C1:C110,AM1:AM110)

Now, I would expect this to return whatever value is in column AM in the
same row where a value in column C is "Text" in the first sheet.  However,
what it actually puts in the cell is 0.  Seriously, I made sure that the row
in question is between 1 and 110, I changed the cell contents of the AM
column to a number (instead of a formula that produces a number), and I
still see 0 in my lookup cell.  What gives?  I mean, seriously!

So then I make a completely new sheet, no formulas, no nothing, and I put
Text (the word text) in cell C10, and 145 in cell AM10.  I then make a
completely new Lookup formula in another workbook, and use the same formula,
only referring to the new sheet in the new book:

=LOOKUP("Text",'[NewWorkBookName.xls]NewWorksheetname'!C1:C110,AM1:AM110)

Again, I get 0.  Not an error, not #Name, just 0.

What could possibly cause this?  Is there some weird thing I don't know
about the use of Lookup?  Is the syntax not right?

Any help appreciated, and thanks.