Explain the logic of MATCH(COUNTIF in ARRAY {}

[theta]

When using COUNTIF in an array with MATCH, I have to swap the range and criteria to get it to analyse properly

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>APPLE</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:fmla="=A1">APPLE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>APPLE</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">PEAR</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>APPLE</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BANANA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEAR</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">MANGO</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEAR</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEAR</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BANANA</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BANANA</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MANGO</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR></TBODY></TABLE>

The example formula for this cell is : =INDEX($A$1:$A$10,MATCH(0,COUNTIF($B$1:$B2,$A$1:$A$10),0)) entered as an array

When I evaluate the formula, the countif is behaving as if the full range is the blue segment, giving 1 or 0 depending if the green elements are satisfied. Doesn't behave like this outside of MATCH. It behaves in reverse, comparing the range to the criteria.

Can somebody please explain the full logic behind this mechanism...want to understand the logic so I can utilise it better

 

[Sandeep Warrier]

Hi

It is not behaving the reverse. It is behaving exactly like COUNTIF should.

Consider

Excel Workbook
	A	B
1
2	APPLE	APPLE
3	APPLE	PEAR
4	APPLE	BANANA
5	PEAR	MANGO
6	PEAR	#N/A
7	PEAR	#N/A
8	BANANA	#N/A
9	BANANA	#N/A
10	MANGO	#N/A
Sheet1

Excel 2003

The formula in B2 is

=INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:$B1,$A$2:$A$10),0))

The COUNTIF part evaluates to

COUNTIF($B$1:$B1,$A$2:$A$10) - Since this is array entered, it is looking for all elements of the range A2:A10 in the range B1:B1. Since B1 is empty, none of the elements A2:A10 exist and hence COUNTIF returns an array of 0

{0;0;0;0;0;0;0;0;0}, which when used with MATCH

MATCH(0,{0;0;0;0;0;0;0;0;0},0) returns 1 and so INDEX(A2:A10,1) returns Apple

Consider B3

=INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:$B2,$A$2:$A$10),0))

COUNTIF($B$1:$B2,$A$2:$A$10) => looks for all elements A2:A10 in B1:B2. B1 is empty and B2 = Apple.... Thus COUNTIF returns

{1;1;1;0;0;0;0;0;0} => since the first 3 elements in A2:A10 is Apple and Apple is present once in B1:B2

MATCH(0,{1;1;1;0;0;0;0;0;0},0) returns 4

INDEX(A2:A10,4) returns Pear

and so on.

Hope this helps.

 

[theta]

So what do you mean "Since this is array entered, it is looking for all elements of the range A2:A10 in the range B1:B1"

If it wasn't array entered it would be acting the opposite way, looking at all criteria (B1:B1) that are present in the range (A2:A10) ?

But when array entered it works in reverse?

 

[Sandeep Warrier]

No.

If it is not array entered, even though the formula says COUNTIF(B1:B1,A2:A10), it would look for only A2 in B1:B1, which will return a 0 since B1 is empty. Now since the COUNTIF is present in the Lookup Range part of MATCH, a single value 0 errors out. On array entering the formula, COUNTIF, within MATCH, returns a range of values.

So whether it is array entered or not, it always looks for A2:A10 in B1:B1 and so on.

 

[theta]

Hmmm...i am still confused...looking at the structure of the formula it is set out =COUNTIF(RANGE,CRITERIA)

So for me this would mean if the range was A,B,C,D,E,F,G and the criteria was A,B,C - it would count how many times the criteria appears in the range

But looking at the original exmaple in array form. We are seeing how many items from the range are present within the criteria. Our range was only a few cells, our critieria was several cells but the logic workings covered all of these cells 0,0,0,0,0,1,0,0,1 etc

Or is this basically representing each item in the criteria being matched against the range (in our case the items already "found") ?

 

[rny1mrxl]

theta/sandeep warrier,

i greatly appreciate the original question AND the explanation of index/match/countif. i modified it a bit as follows and drive

it from an excel 'table' that gets refreshed each week.

=IFERROR(INDEX(Table7[name],MATCH(0,COUNTIF($A$1:A1,Table7[name]),0)),"")
[and you make it an array formula]

problems:
1. if table7 refreshes[using the excel 'refresh-all' button], and rows are added, the array formula is

locked [can't add rows]
2. if table7 refreshes, and rows are deleted, the array formula winds up showing a 0 in the row that

was deleted instead of #NA[which it did before the IFERROR function was added], which is fine, but

still comes up short

however Microsoft implemented 'refresh-all', to wipe out a original table, read in all new values from

the source, expanding or contracting the size of the resulting table, well that I can't figure out how

to do. and i don't know how to link/cascade/[whatever] an additional 'table' to the first 'table'.


maybe you can explain if there is a way to make the result-set array dynamic? do you think this can be done with formula or does it require some kind of event driven vba code?

thanks,
ron

 

[Aladin Akyurek]

@rny1mrxl

If the formula figures in a column next to the name data, say, column A houses the 'name' values, column B houses the resultant set of unique names, as shown below:

name	#List#
APPLE	APPLE
APPLE	PEAR
APPLE	BANANA
PEAR	MANGO
PEAR	KIWI
PEAR	MULBERRY
BANANA
BANANA
MANGO
KIWI
MULBERRY

<TBODY>
</TBODY>

The formula should be modified...

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX([name],
  MATCH(0,COUNTIF(OFFSET($B$1,0,0):$B1,[name]),0)),"")

If the unique names are the outside table, say, in column D, as shown below:

$LIST$

APPLE

PEAR

BANANA

MANGO

KIWI

MULBERRY

<TBODY>
</TBODY>

The formula is...

D2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Table7[name],
  MATCH(0,COUNTIF($D$1:$D1,Table7[name]),0)),"")

See the workbook implementing the foregoing:
https://dl.dropboxusercontent.com/u/65698317/rny1mrxl unique sublist using Excel table.xlsx

 

[rny1mrxl]

@rny1mrxl

If the formula figures in a column next to the name data, say, column A houses the 'name' values, column B houses the resultant set of unique names, as shown below:

name	#List#
APPLE	APPLE
APPLE	PEAR
APPLE	BANANA
PEAR	MANGO
PEAR	KIWI
PEAR	MULBERRY
BANANA
BANANA
MANGO
KIWI
MULBERRY

<tbody>
</tbody>

The formula should be modified...

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX([name],
  MATCH(0,COUNTIF(OFFSET($B$1,0,0):$B1,[name]),0)),"")

If the unique names are the outside table, say, in column D, as shown below:

$LIST$

APPLE

PEAR

BANANA

MANGO

KIWI

MULBERRY

<tbody>
</tbody>

The formula is...

D2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Table7[name],
  MATCH(0,COUNTIF($D$1:$D1,Table7[name]),0)),"")

See the workbook implementing the foregoing:
https://dl.dropboxusercontent.com/u/65698317/rny1mrxl unique sublist using Excel table.xlsx

+++++++++++++++++

Aladin,
thankyou sir. this has been a really good leaning journey for me.

as I learn, I played some, made a selection in my original file from a dropdown filter. the execution is slow. not a problem. still learning.
so, I tried a benchmark.
I loaded the original into access and did:

select xxxx into 'new table'
from 'old table'
group by 'name'
order by 'name'

this is 'immediate'.

I think you an write sql in excel2013?
i want to try that against the 'original' table.
I don't know how, but that is next step.

thankyou for teaching.
ron

 

[Aladin Akyurek]

+++++++++++++++++

Aladin,
thankyou sir. this has been a really good leaning journey for me.

as I learn, I played some, made a selection in my original file from a dropdown filter. the execution is slow. not a problem. still learning.
so, I tried a benchmark.
I loaded the original into access and did:

select xxxx into 'new table'
from 'old table'
group by 'name'
order by 'name'

this is 'immediate'.

I think you an write sql in excel2013?
i want to try that against the 'original' table.
I don't know how, but that is next step.

thankyou for teaching.
ron

You are welcome. Thanks for providing feedback.