# Match(NamedRange,NamedRange,0) doesn't work

#### Jacophile

##### Board Regular
Hi all, I hope someone can spot the problem here.

I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW(\$C2)+1),1,0))}

where Group is: OFFSET(Groups!\$C\$2,0,0,Groups!\$D\$1)

and the array formula is entered in Groups!E1 and returns an #N/A error.

If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.

If it calculates OK using F9, why can't excel resolve it in the cell??

When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.

The Groups array in column C is an array formula entered over 388 rows and contains:
{=IF(\$A2:\$A389="","",INDEX(XCorel!\$IC\$2:\$IC\$232,\$A2:\$A389))}
where XCorel is another sheet in the same workbook.

Can anyone suggest why this might be?
If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.

Last edited:

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### MrExcel MVP
What is in Groups!\$D\$1?

And what does the array formula compute?

#### Jacophile

##### Board Regular
Hi, thanks for the reply, I have just finished installing excel jeanie so here is the layout:

Excel Workbook
ABCDEFGH
10.8CountGroup201#N/A48#N/A201
233139111
Groups

Here is a snippet from the table in the XCorel sheet that these formulae are reading:

Excel Workbook
HZIAIBIC
1Group CountGroup'LeafGroup
20 FALSE
30 FALSE
XCorel

If I do copy, paste values into column IC here, the problem also goes away.

Last edited:

#### Jacophile

##### Board Regular
Basically I have 231 items, some of which are co-related, the ones that are co-related are in a group which is uniquely defined by the ultimate member of that group.

Groups!D1 contains the count of the 231 items that are in a group, in this case 201. In other words 30 of the items are not co-related with any other item.

Column A in the Groups sheet contains a list of all items that qualify as a group member and Column C contains which group they are in.

The array formula is to compute the number of unique elements in column C. So it just counts the number of elements from the match(group,group,0) formula that match the index number in group, i.e. it counts the first occurrences of the elements in group.

Last edited:

#### Jacophile

##### Board Regular
In other words Groups!D1 is the number of elements in the list in column C...

##### MrExcel MVP
In other words Groups!D1 is the number of elements in the list in column C...

If that cell house a formula, I do want to see that...

BTW, a better way of counting distinct items is:

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH("~"&A1:A5,A1:A5&"",0)),ROW(A1:A5)-ROW(A1)+1),1))

#### Jacophile

##### Board Regular
Groups!D1 is a reference to a named range in the XCorel sheet containing the formula =COUNT(IC2:IC232). The development of column IC is shown in the excel jeanie post above.

#### Jacophile

##### Board Regular
The other method you suggest for computing the count has the exact same behavior as mine: when I enter it initially it has the correct result, but as soon as I hit F9 and the sheet recalculates, it reverts to #N/A. It is in I1 below.

Excel Workbook
ABCDEFI
10.8CountGroup201#N/A48#N/A
23313923111
Groups

##### MrExcel MVP
Groups!D1 is a reference to a named range in the XCorel sheet containing the formula =COUNT(IC2:IC232). The development of column IC is shown in the excel jeanie post above.

Is the result of this formula

=COUNT(IC2:IC232)

used in the definition of Group, which is:

OFFSET(Groups!\$C\$2,0,0,Groups!\$D\$1)

If so, why are you not using the C-range itself in Groups directly?

Replies
16
Views
600
Replies
4
Views
246
Replies
0
Views
311
Replies
5
Views
435
Replies
3
Views
595

1,190,898
Messages
5,983,467
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back