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

#### SteveThePirate

##### New Member
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.

 Results Alphabet_Column Number_Column Input_Value 1 AA 1 AA 2 Baa 2 5 A 3 7 D 4 9 AA 5 F 6 AA 7 H 8 AA 9

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Weazel

##### Well-known Member
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

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

#### SteveThePirate

##### New Member
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

##### Well-known Member

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

#### SteveThePirate

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

#### Michael M

##### Well-known Member
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 !!!

Last edited:
• SteveThePirate

#### SteveThePirate

##### New Member
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.

Replies
8
Views
162
Replies
5
Views
80
Replies
11
Views
116
Replies
17
Views
260
Replies
9
Views
65