Help importing data from one table to another based on a criteria set.

[Drakena]

Hi,

I need some help trying to figure out why the following is not working, I have a data table on one sheet that is exported from an external database. I then have a summary table that contains the results of search and other data which is then published onto an internal site. Below is sample data used to illustrate the issue:

Table 1 (Sheet: Export_Data)

	A	B	C
1	ID	Name	Subject
2	1234	Brad	[English] [Science]
3	1235	Sharon	[Math] [Science]
4	1236	Mike	[Math] [English] [Psychology]

<tbody>
</tbody>

Desired Results:
Table 2 (Summary_English Students)

	A	B
1	ID	Name
2	1234	Brad
3	1236	Mike

<tbody>
</tbody>

Formula Currently In Use on B2 in Table 2:

{=IFERROR(INDEX(Export_Data!A$2:A$50,SMALL(IF(MATCH("*"&"English"&"*",Export_Data!$C:$C,0),ROW(Export_Data!A$2:C$500)-ROW(Export_Data!A$2)+1),ROWS(Export_Data!A$2:Export_Data!A2))),"-")}

When searching through the Names I am able to pull out the required data, however when searching through the Subject column the results to not provide me with the actual results. I have over 300 entries to filter through and would prefer not to do this by hand, but instead just display the ID and Name of all students studying English.

I cannot tell if it is [square brackets] causing the issues, seeing as that is the only difference in the columns. The data is exported from the external database like this and cannot be changed, so I need to find a way to search for and filter the data as it is and display is on the second worksheet.

Any help at all with this would be greatly appreciated.

Thank you.

 

[pbornemeier]

Square brackets are used in formulas to separate workbook names from worksheet names. This may be what is causing the problem, although I am not sure why it would in this case.

Can you add a helper column using a formula with 2 SUBSTITUTE functions that would change each [ or ] to a space (or other harmless character) such as:
D2 = =SUBSTITUTE(SUBSTITUTE(C2,"[",""),"]","")
and then point the failing formula to that column?