VLOOKUP always returning the same value!

[Jennifer Cali]

I'm an experienced Excel user, and have run into something beyond strange. My
vlookups (whether manually entered or run as part of an existing macro) will
now ONLY return the looked-up value for the first entry when I drop OR paste
the contents of the first cell down.

FOR EXAMPLE: If I have two sheets - one with names and phone numbers and one
with names and birthdays, and i try to do a vlookup on name between the two
sheets, it looks like this:

Sam 3/29/78 414-323-4132
Sally 5/23/68 414-323-4132
Tara 2/2/34 414-323-4132

I can then continue to paste the copied (original) formula beside blank
cells, and it will still continue to return the 414-323-4132 number.
STRANGE! - and FRUSTRATING!!!

Can someone help? Is there a setting I've inadvertently clicked? I've
already verified data types, forms, etc...the standard stuff. I've also shut
down/restarted, etc...nothing. I know it's not the reports b/c the VLOOKUP
works on other computers on the same reports. WHAT DID I DO?!?

 

[JP]

Just based on your sample, the phone numbers are all the same, so that
might explain why a VLOOKUP on Sam and Sally would appear to return
only Sam's phone number.


HTH,
JP

 

[Jennifer Cali]

Sorry, the example was the problem I'm having. Sally has a different number
than Sam who has a different number than Tara.

 

[JP]

Are you using FALSE as the last argument of VLOOKUP? That would ensure
that only an exact match is found. For example

=VLOOKUP("Sam",A1:C100,3,FALSE)

Assuming your data was in A1:C100.


HTH,
JP

 

[Jennifer Cali]

Yep, I'm definitely including the FALSE.

The other *odd* thing I see is even AFTER the cells caluculate, the workbook
continues to say "CALCULATE" in the bottom left corner. Thoughts?

 

[Tyro]

May I see your data and your formula?

Tyro

 

[JP]

I'd follow Tyro's suggestion and post some real sample data, along
with the formula you are actually using.

Regarding the "Calculate" message, it's difficult to debug what's
going on because we have no way to tell what your worksheet
environment looks like. If you have too many formulas (esp array
formulas), other design elements, fonts, etc that message can remain
permanently.

Check out this KB:

http://support.microsoft.com/kb/243495


HTH,
JP

 

[Jennifer Cali]

Ok, I *think* I have it working based on something in the attachment. I hit
the F9 key per email link below, and the cells calculated. I researched the
F9 key and found this: Press F9 Calculates formulas that have changed since
the last calculation, and formulas dependent on them, in all open workbooks.
If a workbook is set for automatic calculation, you do not need to press F9
for calculation.

Based on this reasoning, I'm assuming that I've somehow SHUT OFF the
automatic calculation button. How do I turn it back on?

 

[Jennifer Cali]

Never-mind. Found it. Under OPTIONS, I changed the CALCULATION to be
AUTOMATIC.

 

[JP]

Glad to hear it was something simple.


--JP