FILTER formula with criteria not working

[JeBickel]

Ok so I have 4 tabs worth of information: 

1) Site information

2)Company contact information

3) Company scope (which is what work types each company can do)

4) Company Span (which is which locations each company can work at)

Tabs 1 and 2 are unique entries and tabs 3 & 4 enter multiple companies and multiple sites. 

I wanted to make a 5th tab with a search so the user can enter a branch, and a work type, from a dropdown box then return the contact information for all companies that meet that criteria but I cannot get it to work!

I've never used this function before; I did confirm I'm on O365.  please see attached file with the tables in them. all tables are named and the two search fields are also named.

[#2]

@JeBickel 

I'd love to help. I find the FILTER function amazing, one of the best features ever.  BUT the way you've laid out your tables and your named ranges (!!) is actually interfering with clarity and simplicity.

It's difficult for me to be sure what's going wrong here, but IF I'm following what you want to do, one of the difficulties is that you've got too many tables here....FILTER will go through one table at a time, and the selection criteria need to be in that same table in order for it to work.

It looks to me as if, the way you've laid out the data, you would need to search through two tables (or more!) in order to find a vendor profile that meets the combined criteria of Branch # and Work Type 

It's possible that Power Query--which on a Mac I do not have--could work for you; that's different from FILTER and does allow the use of multiple tables far more readily than does FILTER. For that I'll defer to some of the other Excel experts.

If it turns out that you too are on a Mac, then I suggest we'd need to re-organize some of the tables. I just discovered that you've hidden some sheets...notably the ones containing lists that are used for data validation and other purposes; it would have been helpful to those of us from whom you're requesting help had you not done so; it's a good practice when the system is working, but while under development, keeping such things visible is good practice. 

It's also clear from some of the diagrams that you're quite sophisticated in database design, perhaps having worked on full relational databases in the past. That would fit too with the way you've laid things out, working toward "data normalization." And that would also suggest that Power Query is more what you need than FILTER; the latter works well with a non-relational (flat file) database.

So as noted above, I'm going to defer to some of the others here in this forum who are versed in Power Query and associated tools.

[#3]

@mathetes Thanks for the reply, apologies, I meant to unhide those sheets but I must not have hit Save.

yes, I'm familiar with databases in a passing manner; more in that I understand the logic than any skills in building them.  The goal is for this sheet to eventually be uploaded into a Vendor management module (hence my attempting to lay it out as close to import format as possible) but that is month's down the road and I've been tasked to develop an interim solution.

I've never used FILTER function before as I've only just gotten on 365. so it only searches one table at a time? How would I create a single criteria search? say the company name against the branch location? Could I then apply INDEX Match to the other columns to pull the relevant contact information?

I've also never used Power Query, but it does sound like that may be more in line with what I need? I wish I could put this on a single master table but in the full database I have 225 branches, ~400 vendors and 12 work types that could apply to each vendor... I can't think of a way to get it in any other format....

It could be that my most simple stopgap solution is a tiny little baby Access Database but i'm trying to avoid that if I can.

Thanks for the response! 

[#4]

@JeBickel 

I learned about FILTER (along with a couple other of the Dynamic Array functions) from this video. After that, it was all trial and error, but the video is great. https://www.youtube.com/watch?v=9I9DtFOVPIg

And I wondered about Access: haven't used it in years (having retired nearly 20 years ago, I just work with my own files now, so don't really need that kind of power).....I too have more of a "logic" connection with relational databases than an actual hands on, at least current. Back thirty years ago I was pretty good with SQL, querying an IBM mainframe database, extracting data to Excel (or Access), and was involved as the director (on the client side) of an HR and Payroll database for a major corporation.....so I learned some of the jargon as well as actually just having fun. The logic of normalization of a database is quite fascinating.....and for the sake of efficiency may be exactly what you need. Power Query does make it possible, from what I understand, within Excel.....so best wishes.