Indexing and matching multiple criteria

[Dbeethekidd]

Hi All

I'm trying to create a user friendly database that will display various contact details when certain criteria are inputted by the user.

The user will be required to choose from a list of numbers that identify buildings and a list of services provided for that building.

For example.

User selects number "1" for building A and selects "Cleaning" as the service they want for the building. The output should return all of the cleaners details for that specific building. Then if the building changes, the details should change to match the newly selected building and likewise with the services.

I'm unsure as to whether I should have all the service details on 1 tab or seperate tabs for each as they need to be referenced to individual building numbers.

I will try to upload my SS though I seem to be having some issues with my office firewall

If anyone can help it would be much appreciated as I have reached an impassable wall with my current skill level.

If you need anymore information please let me know

Many many thanks again

DB

[NBVC]

If you place all building numbers in column A and all associated services in column B, then all specific details in adjacent columns, then you can use Index/match..

e.g..

=INDEX(Sheet2!C$1:$C$100,MATCH(INDEX((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100=$B2),0),0))

Where Sheet2 is the tab you have the database in...and you want to match A2 and B2 to columns A&B of that sheet, then start returning data from column C...

You can copy the formula down and across to get all relevant data for multiple building codes and services.

Note: Adjust sheet name and ranges to suit.

[Dbeethekidd]

Thanks for the quick response, but i don't think i've quite grasped that and from I can see it isn't quite what I was looking for.

Basically i need the 2 options chosen by a user to return not only a supplier reference but all of their details for that site.

for example.

If i chose building "1" and the service "cleaning" from my 2 droplists, the cells at the bottom would return all the contact details for the cleaners at that particular site.

If however I chose another site, the details would change to match that sites cleaners details. So if I chose building 2 it would return a new name, number and email address for that building.

I need to have different worksheets for the supplier details that will be extracted and placed on the main front screen when the 2 criteria have been chosen.

I have attached the spreadsheet to give a better understanding of what i'm looking for.

Many many thanks again

DB

[simjambra]

I tweaked it a bit to include a VLOOKUP. Plus I have added a few named ranges.

How's this for you?

[Dbeethekidd]

That looks awesome!

Can I safely assume that if I added more details to individual ranges I could expand it significantly in size as I will need to include a couple hundred buildings and about 50/60 suppliers with individual details?

[simjambra]

That looks awesome!

Can I safely assume that if I added more details to individual ranges I could expand it significantly in size as I will need to include a couple hundred buildings and about 50/60 suppliers with individual details?

I might be corrected on this but I always Select the table, right-click and select Create List. This will expand the named ranges as you add more data.

Alternatively, you could insert all of your data. Go to Insert -> Name -> Define and make sure the named ranges relate to the ranges that match the data. This way you have set everything up, established all of the data and away you go.

[simjambra]

Also if you use named ranges, you can create a Data Validation Drop down list over different worksheets.

e.g. If you create a list of Buildings, select Insert - Name - Define and name that Range (e.g. buildings), when you go onto your first sheet and select the cell for the drop down, select Data - Validation... List and the source of the list is = and the name of your range (e.g. =buildings)

[Dbeethekidd]

Thank you very much again Simjambra,

I've used the create list option as you suggested and it works perfectly

You have saved me many hours of stress with this and vastly improved my understanding of the Indexing/Matchin and VLookup functions

Many Thanks again

DB

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save