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)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Name
[/TD]
[TD]Subject
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]Brad[/TD]
[TD][English] [Science][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1235[/TD]
[TD]Sharon[/TD]
[TD][Math] [Science][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1236[/TD]
[TD]Mike[/TD]
[TD][Math] [English] [Psychology][/TD]
[/TR]
</tbody>[/TABLE]
Desired Results:
Table 2 (Summary_English Students)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID
[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]Brad[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1236[/TD]
[TD]Mike[/TD]
[/TR]
</tbody>[/TABLE]

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?