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

SteveThePirate

New Member
Joined
Aug 8, 2018
Messages
4
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.

ResultsAlphabet_ColumnNumber_ColumnInput_Value
1AA1AA
2Baa2
5A3
7D4
9AA5
F6
AA7
H8
AA9

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

Some videos you may like

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
Joined
Dec 24, 2011
Messages
3,155
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
Joined
Apr 4, 2017
Messages
74
=IFERROR(AGGREGATE(15,6,(1/(Alphabet_Column=Input_Value))*Number_Column,ROWS(A$1:A1)),"")
 

SteveThePirate

New Member
Joined
Aug 8, 2018
Messages
4
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
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 8, 2018
Messages
4
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
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 8, 2018
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top