Bizarre behavior in VLOOKUP function
 

I have a workbook with two tables, the first containing rooms and the second
containing department codes and department names. I am trying to use VLOOKUP
to display the department name next to the alphanumeric department code on
each row of the first table (column C):

First table (Rooms):

RC A B C
v
5 Room # Dept Code Dept Desc
6 1023 PH253 = VLOOKUP(B6,'DeptCodes'!$A$2:$C$701,2)
..
..

Second Table (DeptCodes, sorted alphabetically)

RC A B
v
1 Dept Code Dept Desc
..
..
224 PH253 Physics
..
..

Here is the bizarre behavior:

1) When I try to copy to formula from C6 to the cells below (C7, C8, etc.),
the cell reference does not change. It stays set to C6, even though I have
not entered it as an absolute cell reference. I want to use relative cell
references precisely so that I can propagate the formula throughout the
entire column and have it look up the department for each room. So, that's
not working...

2) When I am in cell C6 and I click in the formula editing box, then on the
fx symbol to call up the function arguments, everything looks right, and the
result evaluates to "Physics", which is correct. But when I press "Ctrl-`"
to toggle out of formula auditing mode, the formula results are not
displayed, just the column widths increase.

3) When I am in cell C6 and click on "Tools", "Formula auditing", "Evaluate
Formula", the result of the evaluation says, "The cell currently being
evaluated contains a constant."

It's as if the formula (which is correct, isn't it?) is being treated as a
text string in one area of the program and as a formula in another area of
the program. Apparently, the cell reference ("C6") is being treated as a
constant instead of a cell reference.

Can anyone explain this flaky behavior? How do I get around this??? Help
is greatly appreciated!!

--
Michael Boydston-White
Project Manager,
City College of New York

Bizarre behavior in VLOOKUP function
 

Make sure that C6 is formated as General (not Text).

Then reenter the formula
(Select C6, Hit F2, then enter)

And I would think that since you're matching up on a text value in B6, you'd
want an exact match:

=VLOOKUP(B6,DeptCodes!$A$2:$C$701,2,False)

If this doesn't help, make sure you don't have any leading spaces in front of
that initial equal sign.

Michael Boydston-White wrote:

I have a workbook with two tables, the first containing rooms and the second
containing department codes and department names. I am trying to use VLOOKUP
to display the department name next to the alphanumeric department code on
each row of the first table (column C):

First table (Rooms):

RC A B C
v
5 Room # Dept Code Dept Desc
6 1023 PH253 = VLOOKUP(B6,'DeptCodes'!$A$2:$C$701,2)
.
.

Second Table (DeptCodes, sorted alphabetically)

RC A B
v
1 Dept Code Dept Desc
.
.
224 PH253 Physics
.
.

Here is the bizarre behavior:

1) When I try to copy to formula from C6 to the cells below (C7, C8, etc.),
the cell reference does not change. It stays set to C6, even though I have
not entered it as an absolute cell reference. I want to use relative cell
references precisely so that I can propagate the formula throughout the
entire column and have it look up the department for each room. So, that's
not working...

2) When I am in cell C6 and I click in the formula editing box, then on the
fx symbol to call up the function arguments, everything looks right, and the
result evaluates to "Physics", which is correct. But when I press "Ctrl-`"
to toggle out of formula auditing mode, the formula results are not
displayed, just the column widths increase.

3) When I am in cell C6 and click on "Tools", "Formula auditing", "Evaluate
Formula", the result of the evaluation says, "The cell currently being
evaluated contains a constant."

It's as if the formula (which is correct, isn't it?) is being treated as a
text string in one area of the program and as a formula in another area of
the program. Apparently, the cell reference ("C6") is being treated as a
constant instead of a cell reference.

Can anyone explain this flaky behavior? How do I get around this??? Help
is greatly appreciated!!

--
Michael Boydston-White
Project Manager,
City College of New York

--

Dave Peterson

Bizarre behavior in VLOOKUP function
 

Not that it would fix this problem, but I would recommend using the FALSE
option on the end of your VLOOKUP formula in this instance.

It seems you have a good understanding of what's going on, or not going on,
and indeed it is not operating properly. I think I would try copying some of
my Sheet 1 data over to a new sheet and testing the VLOOKUP formula
there.....and if that didn't produce good results, them try copying part of
the lookup table to another sheet.......the idea being, to try to isolate and
see if the problem can be related to a specific sheet.......if it works, the
offending sheet can be replaced......if not, the whole workbook may have to
be.........

hth
Vaya con Dios,
Chuck, CABGx3



"Michael Boydston-White" wrote:

I have a workbook with two tables, the first containing rooms and the second
containing department codes and department names. I am trying to use VLOOKUP
to display the department name next to the alphanumeric department code on
each row of the first table (column C):

First table (Rooms):

RC A B C
v
5 Room # Dept Code Dept Desc
6 1023 PH253 = VLOOKUP(B6,'DeptCodes'!$A$2:$C$701,2)
.
.

Second Table (DeptCodes, sorted alphabetically)

RC A B
v
1 Dept Code Dept Desc
.
.
224 PH253 Physics
.
.

Here is the bizarre behavior:

1) When I try to copy to formula from C6 to the cells below (C7, C8, etc.),
the cell reference does not change. It stays set to C6, even though I have
not entered it as an absolute cell reference. I want to use relative cell
references precisely so that I can propagate the formula throughout the
entire column and have it look up the department for each room. So, that's
not working...

2) When I am in cell C6 and I click in the formula editing box, then on the
fx symbol to call up the function arguments, everything looks right, and the
result evaluates to "Physics", which is correct. But when I press "Ctrl-`"
to toggle out of formula auditing mode, the formula results are not
displayed, just the column widths increase.

3) When I am in cell C6 and click on "Tools", "Formula auditing", "Evaluate
Formula", the result of the evaluation says, "The cell currently being
evaluated contains a constant."

It's as if the formula (which is correct, isn't it?) is being treated as a
text string in one area of the program and as a formula in another area of
the program. Apparently, the cell reference ("C6") is being treated as a
constant instead of a cell reference.

Can anyone explain this flaky behavior? How do I get around this??? Help
is greatly appreciated!!

--
Michael Boydston-White
Project Manager,
City College of New York

Bizarre behavior in VLOOKUP function
 

And don't hit CTRL + ` which puts you into "View Formula" mode.

That would be the reason for the width increase.


Gord Dibben MS Excel MVP

On Tue, 23 Jan 2007 12:12:13 -0600, Dave Peterson
wrote:

Make sure that C6 is formated as General (not Text).

Then reenter the formula
(Select C6, Hit F2, then enter)

And I would think that since you're matching up on a text value in B6, you'd
want an exact match:

=VLOOKUP(B6,DeptCodes!$A$2:$C$701,2,False)

If this doesn't help, make sure you don't have any leading spaces in front of
that initial equal sign.

Michael Boydston-White wrote:

I have a workbook with two tables, the first containing rooms and the second
containing department codes and department names. I am trying to use VLOOKUP
to display the department name next to the alphanumeric department code on
each row of the first table (column C):

First table (Rooms):

RC A B C
v
5 Room # Dept Code Dept Desc
6 1023 PH253 = VLOOKUP(B6,'DeptCodes'!$A$2:$C$701,2)
.
.

Second Table (DeptCodes, sorted alphabetically)

RC A B
v
1 Dept Code Dept Desc
.
.
224 PH253 Physics
.
.

Here is the bizarre behavior:

1) When I try to copy to formula from C6 to the cells below (C7, C8, etc.),
the cell reference does not change. It stays set to C6, even though I have
not entered it as an absolute cell reference. I want to use relative cell
references precisely so that I can propagate the formula throughout the
entire column and have it look up the department for each room. So, that's
not working...

2) When I am in cell C6 and I click in the formula editing box, then on the
fx symbol to call up the function arguments, everything looks right, and the
result evaluates to "Physics", which is correct. But when I press "Ctrl-`"
to toggle out of formula auditing mode, the formula results are not
displayed, just the column widths increase.

3) When I am in cell C6 and click on "Tools", "Formula auditing", "Evaluate
Formula", the result of the evaluation says, "The cell currently being
evaluated contains a constant."

It's as if the formula (which is correct, isn't it?) is being treated as a
text string in one area of the program and as a formula in another area of
the program. Apparently, the cell reference ("C6") is being treated as a
constant instead of a cell reference.

Can anyone explain this flaky behavior? How do I get around this??? Help
is greatly appreciated!!

--
Michael Boydston-White
Project Manager,
City College of New York