EXACT( ) with multiple values

[DashboardNovice]

In cell G9, why does the EXACT( ) have to be enclosed inside of the AND( )?

Also, why does the AND( ) have only one argument, instead of multiple arguments?

 

[Hui]

Look at the expression
=AND(EXACT(B9:F9,B9))
if you select the text part of that EXACT(B9:F9,B9)
Now Press F9 it displays {TRUE,TRUE,TRUE,TRUE,TRUE}
Which is the comparisson of F9 with each cell in the range
In this case they are all exact matches and so it displays an array of "True""
The And() parts now looks at the array and sees if they are all true and if they are returns True

If one is wrong
eg: Texas is in one of the cells eg C9
The EXACT(B9:F9,B9) returns
{TRUE,FALSE,TRUE,TRUE,TRUE}
and And() returns False

 

[DashboardNovice]

Look at the expression
=AND(EXACT(B9:F9,B9))
if you select the text part of that EXACT(B9:F9,B9)
Now Press F9 it displays {TRUE,TRUE,TRUE,TRUE,TRUE}
Which is the comparisson of F9 with each cell in the range
In this case they are all exact matches and so it displays an array of "True""
The And() parts now looks at the array and sees if they are all true and if they are returns True

If one is wrong
eg: Texas is in one of the cells eg C9
The EXACT(B9:F9,B9) returns
{TRUE,FALSE,TRUE,TRUE,TRUE}
and And() returns False

That makes sense. When I entered this, instead of doing Ctrl + Shift + Enter, I just did Enter and it still worked even though this is an array. How is that possible?

 

[Peter Bartholomew]

It would work in Excel 365 because that defaults to array formulas. In other versions, the formula may not error if there is a cell from B9:F9 in the same column as the formula. That does not mean it is performing the correct calculation, however, it will be using implicit intersection to pick a single cell out of the array. This only happens on the grid. If you place the formula
= EXACT(B9:F9, B9)
in Name Manager's 'refers to' box for a Name 'exactMatch' (say)
then
= AND(exactMatch)
will work with or without CSE.

 

[DashboardNovice]

It would work in Excel 365 because that defaults to array formulas. In other versions, the formula may not error if there is a cell from B9:F9 in the same column as the formula. That does not mean it is performing the correct calculation, however, it will be using implicit intersection to pick a single cell out of the array. This only happens on the grid. If you place the formula
= EXACT(B9:F9, B9)
in Name Manager's 'refers to' box for a Name 'exactMatch' (say)
then
= AND(exactMatch)
will work with or without CSE.

Thank you.

 

[Hui]

Some formulas are Array Formulas by default eg: Sumproduct