Help with array formula

[alombia]

Hi,

I am using the following array formula to retrieve data from a Table on a sheet.

{=IFERROR(INDEX(MasterTable!$C$2:$C$564,SMALL(IF(MasterTable!$A$2:$A$564=$BR$2,ROW(MasterTable!$A$2:$A$564)-1),ROW(MasterTable!1:2))),"")}

$BR$2 contains the matching criteria. The formula works absolutely fine. However, I would like it to retrieve data with a partial match.
Currently, MasterTable!A2:A564 contains a mixture of numbers and text. Usually in the form of 7, 8, 9, 10, 10a, 11b, 12, 10c etc.
I would like to enter, say, 10 in $BR$2 and retrieve data matching both 10, 10a, 10b etc. Currently, I have to put an exact match in BR2 for the formula to work. In other words, if I put 10, it will ONLY retrieves rows matching 10.

BTW, the original formula was developed by a great person here on the forum

Thanks in advance.

 

[steve the fish]

Just changing your original formula slightly you could try:

=IFERROR(INDEX(MasterTable!$C$2:$C$564,SMALL(IF(LEFT(MasterTable!$A$2:$A$564,LEN($BR$2))=""&$BR$2,ROW(MasterTable!$A$2:$A$564)-1),ROW(MasterTable!1:2))),"")