Lookup works for some rows, not others (VERY strange...)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CompleteNewb@comcast.net - 11 Apr 2007 16:38 GMT

I am responding and reposting, in case someone thinks the issue is resolved
because there was a response. I hope that's OK, I was just desperate for a
solution. Reading should begin from the bottom, I don't know how to make my
newsreader not "top post.":

Thanks for the suggestion. It didn't work, but here's the weird thing:

I put the formula at the top of a column, and drag/filled down, changing the
text criteria to look for in column C in each cell's Lookup formula. SOME
OF THEM WORK, AND SOME DON'T.

I figured, maybe it's some kind of formatting issue, where the formula's
looking for a specific text value somewhere between C1 and C110, but in the
sheet maybe it's text that's formatted as something else. So I format the
entire C column as text. No change; some of the formulas work, some show 0.
So I click in the C cell of one of the values that DOES work (the formula IS
returning what's in the AM column in the row where the C column meets my
text criteria), and I choose the format painter and paint the C cell of one
of the ones that's NOT working. No change; it still shows 0. So I think
maybe this text value is in this column twice; I do a "find," and it's only
there once. Then I copy the text I'm looking for in the formula, and do a
"find," and in the "Find What" box, I paste the text criteria I copied right
out of the formula bar (without the quotes); it FINDS it!!

So, the formula's the same, the formats of the cells are the same between
ones that DO work and ones that DON'T, and I am just absolutely positively
kerflummoxed here. I'm looking RIGHT at the first worksheet, and I can see
the value in AM is not 0, the text value in C is IDENTICAL to what's in my
lookup formula. And again, it's working on a bunch of them, and NOT working
(returning 0) on a bunch, even though it's just a drag/fill and changing the
text values looked for. Again, I FOUND the text in the C column using
edit-->Find, but the FORMULA is not finding it. This is happening, by the
way, on 2 PC's (I thought maybe Excel was corrupt on one).

This is on Windows XP, Office 2002, all updates/patches applied.

PLEASE HELP!!!

Try:

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

In article <TuadndtzG4q40YHbnZ2dnUVZ_syunZ2d@comcast.com>,
"CompleteNewb" <CompleteNewb@comcast.net> wrote:

> 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

Reply to this Message

T. Valko - 11 Apr 2007 18:47 GMT

The LOOKUP function *requires* that the lookup_vector be sorted in ascending
order. If it's not there's no telling what answer you'll get. A lot of
times, but not always, if it's not sorted the result will be the last
referenced cell in the result_vector. If you have empty cells at the end of
the lookup_vector and the same at the end of the result_vector that is
usually where you're getting a result of 0.

If you're looking for *exact* matches use VLOOKUP instead. This does not
require any sorting.

Biff

>I am responding and reposting, in case someone thinks the issue is resolved
>because there was a response. I hope that's OK, I was just desperate for a
[quoted text clipped - 85 lines]
>>
>> Any help appreciated, and thanks

Reply to this Message

CompleteNewb@comcast.net - 11 Apr 2007 21:06 GMT

Biff:

Thanks very much for the explanation of what's going on. How odd that it
needs to be sorted. What a stranglehold on functionality!

But here's the problem with VLookup; they add columns all the time, so my
reference to the number of columns over to the right (or left) of the found
value will not work as soon as they add columns!

Argh!!

> The LOOKUP function *requires* that the lookup_vector be sorted in
> ascending order. If it's not there's no telling what answer you'll get. A
[quoted text clipped - 100 lines]
>>>
>>> Any help appreciated, and thanks

Reply to this Message

T. Valko - 11 Apr 2007 21:21 GMT

You can use a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

Biff

> Biff:
>
[quoted text clipped - 113 lines]
>>>>
>>>> Any help appreciated, and thanks

Reply to this Message

Rate this thread: