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

[CompleteNewb]

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)

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

 

[T. Valko]

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

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

 

[CompleteNewb]

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

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

 

[T. Valko]

You can use a dynamic range:

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

Biff

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

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