Excel crashes when typing "false" in VLookup function

[pcbins]

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[Dave Peterson]

This may help as a workaround--but it's not a solution.

(xl2003 menus)
Tools|Options|Calculation tab|uncheck "Accept labels in formulas"

Maybe you'll be ok????

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[pcbins]

I'm not sure why everyone thinks this will fix it, but, as I tell them, this
option is not checked. I have tried it both ways, restarting excel after each
change. And it makes no difference...

This may help as a workaround--but it's not a solution.

(xl2003 menus)
Tools|Options|Calculation tab|uncheck "Accept labels in formulas"

Maybe you'll be ok????

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[google]

"=vlookup(A2,'Sheet2'!A:A,false)",

This formula has incomplete parameters. The Excel 2000 help file (F1)
defines VLOOKUP funcation as:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You are placing a boolean value (e.g. FALSE) where an integer value
needs to be. To wit, you are placing your desired "range_lookup"
optional value in the required "col_index_num" parameter. While FALSE
can loosely be defined as zero (0) and TRUE as anything NOT FALSE, it
generally isn't good practice to substitute a boolean for an integer
and especially in your case as there is no ordinal column number zero
(0) in a range. In your example perhaps a more appropriate formula
would be:

=VLOOKUP(A2,'Sheet2'!A:A,1,FALSE)

HTHs

 

[pcbins]

Sorry, that was a typo... I've been using this formula for years and years
and years... probably typed it out a thousand times or more...

I know how to use the formula. Please ignore the typo above and focus on the
problem...

 

[Dave Peterson]

Since you multiposted the same question in different places, how would any one
know that you had gotten this response elsewhere?

Good luck.

I'm not sure why everyone thinks this will fix it, but, as I tell them, this
option is not checked. I have tried it both ways, restarting excel after each
change. And it makes no difference...

This may help as a workaround--but it's not a solution.

(xl2003 menus)
Tools|Options|Calculation tab|uncheck "Accept labels in formulas"

Maybe you'll be ok????

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[Ashish Mathur]

Hi,

You table array is the entire column A (that is 65,536 rows). Please reduce
the range to where your data is till and then see whether Excel still
crashes.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[pcbins]

No, it doesnt matter what size the array is. It is rarely the entire column.
RARELY EVER. It can be 500 rows or 5000 or 50000. It makes no difference.

PLEASE ADDRESS THE PROBLEM, MICROSOFT, and provide a fix. I think 5 years is
long enough to have researched it.

Hi,

You table array is the entire column A (that is 65,536 rows). Please reduce
the range to where your data is till and then see whether Excel still
crashes.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[pcbins]

Nevermind, I think I will repost without an example so you can focus on the
real problem...

Hi,

You table array is the entire column A (that is 65,536 rows). Please reduce
the range to where your data is till and then see whether Excel still
crashes.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

While typing a vlookup function such as "=vlookup(A2,'Sheet2'!A:A,false)",
immediately after typing the "f" in "false", Excel will crash with the
following error:

Identify Label
There is more than one cell with this label:f
Select the cell containing the label to use:
[OK] [Cancel]

No matter what is entered following, the following error will occur:

Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience.

This problem has been around for almost 5 years now. Is there a hotfix for
it yet? Or should I give MS another 5 years to work on it?

 

[Glenn]

No, it doesnt matter what size the array is. It is rarely the entire column.
RARELY EVER. It can be 500 rows or 5000 or 50000. It makes no difference.

PLEASE ADDRESS THE PROBLEM, MICROSOFT, and provide a fix. I think 5 years is
long enough to have researched it.

Does this happen in any worksheet you try it in, or only a specific worksheet?
I can't duplicate the problem.

By the way, you may have mistakenly assumed that you are talking to MICROSOFT
EMPLOYEES...the people here, even the MVP's, are volunteers, not generally
affiliated with Microsoft.

 

[JE McGimpsey]

These groups are peer-newsgroups, not official channels for MS. Nobody
here can fix your problem, assuming it exists.

Frankly, given that it, as far as I can tell, has never come up in these
groups before, I suspect it's something unique to your system.
Otherwise, there would have been an outcry from millions of users who
use VLOOKUP.

 

[pcbins]

try Googling it... I accidentally found this "lets help each other since
microsoft won't" forum... millions are out there not knowing where to go for
help when microsoft ignores them...

 

[JE McGimpsey]

try Googling it... I accidentally found this "lets help each other since
microsoft won't" forum... millions are out there not knowing where to go for
help when microsoft ignores them...

I had checked Google groups, and I've just googled ("VLOOKUP crash
FALSE"). Prior to this month, there's nothing relevant back through 2003.

I don't want to be an MS apologist, but if there were really millions of
people having this problem, we would have heard about it here.

That doesn't mean that the problem isn't real - just not very common.

 

[Glenn]

I had checked Google groups, and I've just googled ("VLOOKUP crash
FALSE"). Prior to this month, there's nothing relevant back through 2003.

I don't want to be an MS apologist, but if there were really millions of
people having this problem, we would have heard about it here.

That doesn't mean that the problem isn't real - just not very common.

http://tinyurl.com/bbq9kn

 

[pcbins]

oh my goodness! you are kidding me? Are you sure you are using Google?? I get
pages and pages...

 

[JE McGimpsey]

http://tinyurl.com/bbq9kn

Hmmm.. OK. Didn't show up in

http://tinyurl.com/azb7f7

Still, 1 thread (in 2004) doesn't a million make.

 

[JE McGimpsey]

oh my goodness! you are kidding me? Are you sure you are using Google?? I get
pages and pages...

I get pages and pages of results for that search, but only a few that
mention crashing when typing the 'f' in False - and at least one was
XL02, not X03.

I'm not really interested in continuing the search, and I'm not trying
to be a net nanny. It's obviously a real problem for at least a few
people, but AFAIK, there's been no solutions posted anywhere.

If someone happens to have a solution, I'm sure they'll post it.

 

[David Biddulph]

If you've found pages and pages, why not give us the url of your Google
search?

 

[Glenn]

I get pages and pages of results for that search, but only a few that
mention crashing when typing the 'f' in False - and at least one was
XL02, not X03.

I'm not really interested in continuing the search, and I'm not trying
to be a net nanny. It's obviously a real problem for at least a few
people, but AFAIK, there's been no solutions posted anywhere.

If someone happens to have a solution, I'm sure they'll post it.

The only "solution" posted seems to have been to use a zero instead of typing
"false".