Excel database: services and service providers

Hi all,

Just so I'm not breaking the rules here... I've already posted this elsewhere and I would provide the link, but it's my first post here, so I'm not allowed to include links.

Even if nobody can answer, I'd appreciate if someone could let me know why people aren't replying... maybe my question is too vague/can't be done/asking too much of people to explain. Would be useful to know! I feel like there must be a phrase I could search and then do my own homework, but I don't know if what I'm trying to do has a name like "codependent categories" or something.

Here's the question below - will be indebted to anyone responding:

At our small society, we have members, who both use and supply services. The software we use is ancient (UNIX), so I'm trying to extract data for use in Excel 2016.

To give an example of what I'm trying to do, let's say the services are lawn mowing, dog walking and house cleaning. Not all members supply all services.

Craig provides all three.
Mary only walks dogs.
John walks dogs and mows lawns.

I don't even know what this would look like in Excel, but effectively I'd like to be able to do two things...

1. Type/click on a member and see the list of services they provide e.g. input "Mary" to get the result "dog walking".
2. Type/click on a service title and see a list of members that provide them e.g. input "lawn mowing" to get the results John and Craig.

Any help much appreciated!

I created a "Database" tab which has a list of members in column A and the services they provide in column B. If they provide multiple services, they go on multiple rows.

Then there is a "Lookup" tab. In A2 you input the name of the member you want to return the services for. These appear in column B with the formula:

=IFERROR(INDEX(Database!$B:$B,SMALL(IF(Database!$A:$A=$A$2,ROW(Database!$B:$B),""),ROW()-ROW($B$1))),"")

I have copied this down 10 rows in the example, if you need more rows then just copy it down more.

Similarly you can input the service in D2 and use this in column E:

=IFERROR(INDEX(Database!$A:$A,SMALL(IF(Database!$B:$B=$D$2,ROW(Database!$A:$A),""),ROW()-ROW($E$1))),"")

Hopefully that's the kind of thing you were after. You could also make the input cells a dropdown list, but you would need a separate unique list of services/providers to refer to.

That's brilliant, thank you so much! Really appreciate that you even made a template for me to work from, too.

Nothing further to ask or add, the solution does exactly what I wanted

Glad I could help

Getting ready to post a new thread and saw that I hadn't replied to torachan... it's now a couple of months later but hopefully not too late to say thank you very much for your help!