Populating a new table with data that match criteria from another table.

Rodrigo_ 

Hi, I found this thread and have a similar problem. I was wondering if you might be able to take a look and see if you can spot the solution?

We use Excel to manage logs from many agricultural trials. There is a central 'live' table where live data for all trials gets sent to, but ultimately only the relevant data needs to be sent to each individual trial excel file in a table. Each Trial has a unique code, and I'm trying to use the filter function to populate a table on each individual trial file with data from the live central data table, using the unique code as an identifier. 

Here is a portion of the table:

And here is what's currently working. I'm using the RIGHT function to include just the right 11 characters in the "Trial" column in the FILTER 'include' function, which is the trial code, to pull the relevant data across:

=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(RIGHT(OnlineSprayBookAllData[Trial],11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")

BUT what I'd really like to do is combine the FILTER, MID, and FIND functions to be able to pull the relevant data across even if there are multiple trial codes within the trial column in the live data table. This would make the data entry process much more efficient and we could capture data for multiple trial sites at a time. Like this:

=FILTER(OnlineSprayBookAllData[[Date]:[Photo or Video]],(MID(OnlineSprayBookAllData[Trial],(FIND(Table5[@SITE1],OnlineSprayBookAllData[Trial],1)),11))=Table5[@SITE1],"Please check GOA code is entered in SITE_DATA")

As you can see, It does not work. It does work if you select just a single cell in the FIND 'within_text' portion of the function, but not if you select the whole table column. I've had the same lack of success using the INDEX SMALL functions. Is there anyway I can make this work??

Warmly,

Jos.