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.
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
{=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>