Lookup then Search for text string and return multiple entries

Dear All,

I have a 3 databases each on different sheets within a work book. Different country, different page.
On the first page I have set up a search with two drop down boxes.
The first box lists the country, the second, lists what type of operation the company runs.

I need a way to select the country in the first drop down box then the type of operation and it then lists all the companies from the 3 databases that fit that criteria.
Now I don't have a problem with doing that part.

However the problem comes in the second list box.
Within the database the type of operation for each company is listed in one single cell. E.G. Survey, Police, Passenger, Charter, Ambulance etc.
I don't know how to search for a string of text within the formula to then create an index of all matching companies.
If the type of operation had only one word in it, I don't think it would be too hard but because its listed all in one cell then I've hit a brick wall.

Also I don't know how to do it over 3 different work sheets.
I don't want to combine the databases if I can help it. I have more to add.
I have attached the workbook in case anyone was interested.

Can anyone see an easier way of completing this task if the database was in a different format?

All replies appreciated

T

If you have the option of combining the data the Advanced filter function will work great for you.
In the attached spreadsheet I combined your data, and added a country column and filled it in.

To get the attached sheet to work goto the combined data sheet, and Choose the Country and opperator you want.
They are in yellow at the top.

Then goto the data tab in the ribbon, and choose advanced filter.
I have already set everything up so you only have to click Ok when the window pops up.

You have to re-apply the filter after you change the fields your looking for.
If you change the country, you have to goto data, then advanced filter, and then click ok again.
It doesnt automatically update.



<----Please click the star if this helped you.

In this version I hid all of the columns that you didnt seem to need to see.

Follow the same procedure of choose your variables, then click advanced filter, then ok.




<----Please click the star if this helped you.

Your welcome.
<----Please click the star if this helped you.

Wrap the search terms in "*" example. *passangers* this will find them all. If your using your drop down lists you can add *'s to all of them