EXACT function vs. equals operator

[Greg Lovern]

Where can I find a list of everything Excel's EXACT function does
differently from the equals operator?

I like that EXACT is case sensitive where the equals operator is not,
but sometimes it returns False and I can't find the difference between
the two text strings beting compared, and the equals operator returns
True.


Thanks,

Greg

 

[joeu2004]

Where can I find a list of everything Excel's EXACT function
does differently from the equals operator?

Well, Excel help. But if you are using XL2007, I just learned how
difficult that might be. (I am borrowing someone's computer with
XL2007.) Two options: (1) disable online content by clicking Search,
then Excel Help under On This Computer; or (2) click on List of
Worksheet Functions, etc.

I like that EXACT is case sensitive where the equals operator is not

That's all it does really -- case-sensitive compare for equality.

but sometimes it returns False and I can't find the difference between
the two text strings beting compared, and the equals operator returns
True.

Post what you are comparing and your "exact" formula (pun
intended) ;-). If you are comparing cells, what does TYPE(A1) return
for each cell?

I have tried a few WAGs, but I have not yet stumbled upon an example
that duplicates your observation.

 

[Greg Lovern]

Well, Excel help.  But if you are using XL2007, I just learned how
difficult that might be.  (I am borrowing someone's computer with
XL2007.)  Two options:  (1) disable online content by clicking Search,
then Excel Help under On This Computer; or (2) click on List of
Worksheet Functions, etc.

I'm on XP, and I don't see those choices after clicking Search.
However, the lower-right corner of my help window says "Offline",
indicating "Show content only from this computer".

Where do you see, in Excel help, an explanation of what EXACT does
differently than the equals operator? I looked in Excel 2010 help and
I don't see it there either, and it too says "Offline" in the lower-
right corner of the help window.

Post what you are comparing and your "exact" formula (pun
intended) ;-).  

Unfortunately I've already made changes and I don't get a repro now.

If you are comparing cells, what does TYPE(A1) return
for each cell?

Probably 2, since they are all text; none are numbers, booleans, error
values, or arrays.

I have tried a few WAGs

Wives And Girlfriends of high-profile British soccer players??


Greg

 

[joeu2004]

I'm on XP, and I don't see those choices after clicking Search.

I had to relinquish the Win7/XL2007 computer I was borrowing. But as
I recall, there was a down-arrow next to the word Search. (Although I
think I got the same behavior by clicking on the word Search itself.)
The drop-down menu had "checkboxes". There were two "Excel help"
checkboxes that operated like radio buttons; they were mutually
exclusive. One was under a heading like "online content"; the other
was a heading like "content on this computer".

Where do you see, in Excel help, an explanation of what EXACT
does differently than the equals operator?

It did not. That's the point. But see your other posting regarding
"==".

Wives And Girlfriends of high-profile British soccer players??

Wild Ass Guesses.

 

[alanglloyd]

Where can I find a list of everything Excel's EXACT function does
differently from the equals operator?

EXACT can also be used to check an entry against a range of acceptable
entries, when used in conjunction with OR() as an array formulae.

Alan Lloyd

 

[joeu2004]

EXACT can also be used to check an entry against a range of acceptable
entries, when used in conjunction with OR() as an array formulae.

Example?!

If you mean the following array formulas:

=OR(EXACT(D6,{"ab","cd","ef"}))

=OR(EXACT(D6,X1:X3))

where X1:X3 contains "ab", "cd" and "ef".

This is not a feature of EXACT, of course. It is not even a feature
of OR. It is a behavior of array formulas.

Moreover, you can do the same thing with an equals operator -- and it
does not even require an array formula. To wit:

=OR(D6={"ab","cd","ef"})

Or the array formula:

=OR(D6=X1:X3)

Greg was interested in knowing what "EXACT function does
__differently__ from the equals operator".

 

[alanglloyd]

Example?!

<snip>

Excel Help gives as an example:

{=OR(EXACT(TestValue, CompareRange))}

Alan Lloyd

 

[Bob Phillips]

Where? It only shows a compariuson of single cells in my Excel.

--

HTH

Bob

Example?!

<snip>

Excel Help gives as an example:

{=OR(EXACT(TestValue, CompareRange))}

Alan Lloyd

 

[Jim Cone]

Bob,
From XL97 Help (still the best)...
"To make sure that a user-entered value matches a value in a range, enter the following formula as an array in a cell.
To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for Windows
or +ENTER in Microsoft Excel 97 for the Macintoch.
The name TestValue refers to a cell containing a user-entered value;
the name CompareRange refers to a list of text values to be checked."
{=OR(EXACT(TestValue, CompareRange))}
--
Jim Cone
Portland, Oregon USA



"Bob Phillips" <[email protected]>
wrote in message
Where? It only shows a compariuson of single cells in my Excel.
--
HTH
Bob



<[email protected]>
wrote in message
Excel Help gives as an example:
{=OR(EXACT(TestValue, CompareRange))}
Alan Lloyd

 

[Bob Phillips]

The best? Maybe. Wrong? Definitely.

And they all say Help is getting worse <g>.


HTH

Bob

"Jim Cone" wrote in message
Bob,
From XL97 Help (still the best)...
"To make sure that a user-entered value matches a value in a range, enter
the following formula as an array in a cell.
To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for
Windows
or +ENTER in Microsoft Excel 97 for the Macintoch.
The name TestValue refers to a cell containing a user-entered value;
the name CompareRange refers to a list of text values to be checked."
{=OR(EXACT(TestValue, CompareRange))}
--
Jim Cone
Portland, Oregon USA



"Bob Phillips" <[email protected]>
wrote in message
Where? It only shows a compariuson of single cells in my Excel.
--
HTH
Bob



<[email protected]>
wrote in message
Excel Help gives as an example:
{=OR(EXACT(TestValue, CompareRange))}
Alan Lloyd

 

[Jim Cone]

Bob,
The formula...
"=OR(EXACT(K4,A1:F55))" -or- "=OR(EXACT("Sludge",A1:F55))"
works for me in limited testing, entered as an array formula and checking for entire cell text.
Where/how does it go wrong?
--
Jim Cone
Portland, Oregon USA




"Bob Phillips" <[email protected]>
wrote in message
The best? Maybe. Wrong? Definitely.
And they all say Help is getting worse <g>.
HTH
Bob

"Jim Cone" wrote in message

Bob,
From XL97 Help (still the best)...
"To make sure that a user-entered value matches a value in a range, enter
the following formula as an array in a cell.
To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for
Windows
or +ENTER in Microsoft Excel 97 for the Macintoch.
The name TestValue refers to a cell containing a user-entered value;
the name CompareRange refers to a list of text values to be checked."
{=OR(EXACT(TestValue, CompareRange))}
--
Jim Cone
Portland, Oregon USA



"Bob Phillips" <[email protected]>
wrote in message
Where? It only shows a compariuson of single cells in my Excel.
--
HTH
Bob



<[email protected]>
wrote in message
Excel Help gives as an example:
{=OR(EXACT(TestValue, CompareRange))}
Alan Lloyd

 

[Bob Phillips]

I was referring to Help, not the formula.


HTH

Bob

"Jim Cone" wrote in message
Bob,
The formula...
"=OR(EXACT(K4,A1:F55))" -or- "=OR(EXACT("Sludge",A1:F55))"
works for me in limited testing, entered as an array formula and checking
for entire cell text.
Where/how does it go wrong?
--
Jim Cone
Portland, Oregon USA




"Bob Phillips" <[email protected]>
wrote in message
The best? Maybe. Wrong? Definitely.
And they all say Help is getting worse <g>.
HTH
Bob

"Jim Cone" wrote in message

Bob,
From XL97 Help (still the best)...
"To make sure that a user-entered value matches a value in a range, enter
the following formula as an array in a cell.
To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for
Windows
or +ENTER in Microsoft Excel 97 for the Macintoch.
The name TestValue refers to a cell containing a user-entered value;
the name CompareRange refers to a list of text values to be checked."
{=OR(EXACT(TestValue, CompareRange))}
--
Jim Cone
Portland, Oregon USA



"Bob Phillips" <[email protected]>
wrote in message
Where? It only shows a compariuson of single cells in my Excel.
--
HTH
Bob



<[email protected]>
wrote in message
Excel Help gives as an example:
{=OR(EXACT(TestValue, CompareRange))}
Alan Lloyd