Vlookup in 2007 is now case sensitive

[Heather]

Hello all,

I have been using Vlookup functions for several years in excel 2003 and love
them. They are one of my favorite functions and I use the all the time. My
office recently upgraded to Office 2007 and I noticed that the Vlookup
function is now case sensitive when it was not in Excel 2003. Does anyone
know if there is an option or a check box that makes the False criteria case
sensitive for text. I am using cell references to search between sheets in a
workbook. The vlookup function looks like this

=VLOOKUP(A2,Sheet1!A1:B16,2,FALSE)
A2 = St Als
the matching value on sheet 1 is ST ALS

The only difference in the values is the case. I never had this problem
with Excel 2003 and now several of my worksheets are being affected because
the case doesn't match between sheets. I have already installed service pack
1 for 2007 as well as the add-ins. Any help would be greatly appreciated.

Thanks

 

[Don Guillett]

AfAik vlooKup(in 2007 is NOT case sensetive. Try it with sT alS in the
formula to test. Then try
=VLOOKUP(trim(A2),Sheet1!A1:B16,2,FALSE)
to get rid of any leading\trailing spaces.

 

[Heather]

Hi Don,

Thanks for the reply. The real question here is to see if there is an
option to turn on/off case sensitivity for Vlookup in Excel 2007. It wasn't
case sensitive in 2003 but now in 2007 it is and I don't want it to be. I
have tested this function with different versions of the case including ST
ALS, st als, St Als, ST Als, etc. There are no extra characters or spaces.
The ONLY difference is the case of the text. I even deleted the formula and
entered it again since it might have been affected by converting from 2003 to
2007. The formulas gave values in excel 2003 but in 2007 they give an error
unless I change the case to match exactly. I have done several hundered
vlookup functions so I am very familiar with them.

The trim function is irrelevant at this point since I tested the function
with ST vs St and that did not work either. One of the first things I did
was check for extra spaces. I am 100% sure the problem is the case of the
text. I don't know how to fix it though.

I think when our office set up the global settings for excel they may have
selected something that is affecting the vlookup functions. I am hoping
someone might have some advice on where to look or if that type of option
even exists. I really appreciate the help.

Thanks,
Heather

 

[Peo Sjoblom]

I don't know where you get this from, I was a beta tester (actually a
pre-beta tester as well),
nor am I a big fan of Office 2007 but VLOOKUP is definitely not case
sensitive. I have both 2003, up, 2003 and 2007 on the same computer and
there is no difference. A quick test:

I put this in A1:B2

john 1
John 2

in C1 I put John and the I use this formula

=VLOOKUP(C1,A1:B2,2,0)

according to you I should get 2 since that is the case sensitive match but I
don't,
I get 1 as expected.

It sounds as you don't specify what kind of lookup you want.

To get an exact match you need to use either 0 (like in my example) or FALSE

=VLOOKUP(C1,A1:B2,2,FALSE)

I suspect you do neither


So the answer is that there are no options to turn it on/off

--


Regards,


Peo Sjoblom

 

[Heather]

I have been able to narrow this down to specific workbooks. I think the
problem has something to do with the fact that these workbooks are saved in
PFX Engagement software. We converted to Office 2007 at the same time as we
also upgraded to a new version of PFX Engagement. These fluke occurances are
only affecting files in the engagement software. The vlookup function is
working correctly for workbooks outside of engagement and new files I load
into engagement. It is only converted engagement files that are affected. I
guess this is just an issue with some kind of corruption in the conversion
process. I have another file that the sum doesn't calculate correctly
either. It is off by a few dollars and some change. It looks like this may
be a problem with the engagement software so I will attempt to have their
support team figure out what is happening. Thank you all for the responses
I appreciate the help.

Thanks,
Heather

 

[mauriz.berardi]

Dear Heater,
I found your message on this forum and what happened to you has also happened to me. But... I just opened a file (edited with Excel2003) in Excel2007,and it did not work any more. And, strange but true, there are differencesbetween worksheets in the same document: for example if I write a vlookup formula in the Worksheet1 this would not be case sensitive, while if I write the same vlookup formula in the Worksheet2 this becomes case sensitive. Iam going mad... Did you solve your problem??
Thank you so much!!!
Maurizio


Il giorno martedì 19 agosto 2008 02:37:11 UTC+2, Heather ha scritto: