Adapting array formula to search for strict matches

[Thargz]

<table style="WIDTH: 444pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="592"><colgroup><col style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" width="98"><col style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4266" width="120"><col style="WIDTH: 48pt" width="64"><col style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width="54"><col style="WIDTH: 48pt" span="4" width="64"></colgroup><tbody><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl65" height="19" width="98">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 90pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="120">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="64">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="54">Count</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="64">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="64">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="64">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" width="64">
</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" height="19">Penicillin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">Benzylpenicillin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">001</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" align="right">3</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">001</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">002</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">003</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" height="19">streptomycin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">Benzylpenicillin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">002</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" align="right">0</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl67" height="19">amoxicillin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id="td_post_3062889" class="xl66">Penicillin</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">003</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66" align="right">0</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">
</td></tr></tbody></table>

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

=SUM(IF(ISNUMBER(SEARCH($A2,$B$2:$B$4)),1))

E2, control+shift+enter, copy across, and down:

[FONT=Lucida Console]=IF(COLUMNS($E2:E2)<=$D2,INDEX($C$2:$C$4,[/FONT]
[FONT=Lucida Console]  SMALL(IF(ISNUMBER(SEARCH($A2,$B$2:$B$4)),[/FONT]
[FONT=Lucida Console]   ROW($C$2:$C$4)-ROW($C$2)+1),COLUMNS($E2:E2))),"")[/FONT]

In this example, as I understand, the formula is using a fuzzy search both for the count in D column and for the array in E onwards.

Am I right in thinking, that if I wanted a strict match I could replace column D with this?:

 =COUNTIF(B:B,A)

Does anyone know how to adapt the second array formula so that it would only returns results based on strict matches from the B column to the A column?

 

[Thargz]

For column D I meant pasting

=COUNTIF(B:B,A2)

in D2 and copying/dragging downwards.

 

[AlphaFrog]

Try this in E2

=IF(COLUMNS($E2:E2)<=$D2,INDEX($C$2:$C$4,
  SMALL(IF($A2=$B$2:$B$4,
   ROW($C$2:$C$4)-ROW($C$2)+1),COLUMNS($E2:E2))),"")

 

[Thargz]

Thanks AlphaFrog, that works perfectly!

I think I was able to work around using the original formula by sorting columns A and B alphabetically, but your formula makes it sure and not dependent on sort order of the source.

 

[Thargz]

Just updating this thread with another solution provided by Aladin Ayruek:

Also an option is to switch to MATCH...

D2, just enter & copy down:

=COUNTIF($B$2:$B$4,A2)

E2, control+shift+enter and copy down:

=IF(COLUMNS($E2:E2)<=$D2,INDEX($C$2:$C$4,SMALL(IF(ISNUMBER(MATCH($A2,$B$2:$B$4)),ROW($C$2:$C$4)-ROW($C$2)+1),COLUMNS($E2:E2))),"")

Many thanks for your help again.