How can I amend my Array Formula to extract a series of EXACT matches?

[SteveThePirate]

I am using this array formula to find each occurrence of 'Input_Value' (currently set to 'AA') from the column 'Alphabet_Column' and return the adjacent value in ''Number_Column' in the Results column.

{=IF(Input_Value="","",IFERROR(INDEX(Number_Column,SMALL(IF(ISNUMBER(SEARCH(Input_Value,Alphabet_Column)),MATCH(ROW(Alphabet_Column),ROW(Alphabet_Column))),ROWS($B$2:B2))),""))}

My question is how can I specify a precise match? For example I don't want 'Baa' to satisfy the criteria. I have tried adding ,0 in the MATCH formula but no joy.

[TABLE="width: 522"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Results[/TD]
[TD]Alphabet_Column[/TD]
[TD]Number_Column[/TD]
[TD][/TD]
[TD]Input_Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AA[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Baa[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]AA[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]F[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]AA[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]H[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]AA[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

=IFERROR(INDEX($C$2:$C$10,AGGREGATE(15,6,ROW($C$2:$C$10)-ROW($C$2)+1/($B$2:$B$10=$E$2),ROWS($A$2:A2))),"")

 

[mopp1]

=IFERROR(AGGREGATE(15,6,(1/(Alphabet_Column=Input_Value))*Number_Column,ROWS(A$1:A1)),"")

 

[SteveThePirate]

Thank you to both. Copied and pasted this and it works - now all I need to do is understand what it does and how it works . Your fast responses much appreciated.

 

[Michael M]

Steve......if you are using 2013 or higher, select the cell the formula is in, select the formula tab on the menu bar, look at formula auditing and click on evaluate formula.....it will then show you the breakdown of the formula step by step....HTH.

 

[SteveThePirate]

Another facility that is new to me. Been using Excel for many years and still only scratched the surface. Thank you.

 

[Michael M]

you can also click on the little fx next to the formula bar....if you click somewhere in the formula it will also show what it is doing..AND
always remember the formula works from the inner most brackets outward, not from left to right !!!

 

[SteveThePirate]

Just in case anyone else has looked at this thread, the following is my breakdown of one of the formulas that were suggested. It may help unravel the complexity (well I found it complex )


{=IFERROR(AGGREGATE(15,6,(1/(Alphabet_Column=Input_Value))*Number_Column,ROWS(A$1:A2)),"")}


AGGREGATE is a function that returns a list (or lists in this case) of values (an Aggregate - obviously) based on options specified by the first numeric values. First is the function, second is the option. In this case it is asking for SMALL (15) and Ignore error values (6)
Let's take the formula bit by bit:


(Alphabet_Column=Input_Value)
will return an array of True;False;False;False;True etc


1/(Alphabet_Column=Input_Value)
will create the following
1;Div0;Div0;Div0;1


In the meantime this
Number_Column
will return an array of 1,2,3,4,5,


so
(1/(Alphabet_Column=MrE_Input_Value))*Number_Column
will create the following
1;0;0;0;5


Once copied and pasted into the Results column each separate row is displayed according to the ROWS(A$1:A2) element of the formula which is a neat way of generating an accumulating number.

Thanks again to both of the respondents. Learned a lot this morning.