Simple Systems access matrix I need a formula! Please help

Copper Contributor

Hi everyone, I am trying to build a simple Systems Access Matrix of what users have to what systems.

 

The spreadsheet contains to Sheets

>Data sheet Contains the Reference Data I am trying to Lookup and or Search Data on.

 

>Output sheet is where the FORMULAs will exist to extract the data from DATA sheet. The list of names located in Column A, will be exactly the same than Column A of the Data sheet. FORMULAS will be required to pull data referencing the system name(s) attributed to the Named person on the Data sheet.

 

  1. For each of the system(s) names 
    1. Active Directory
    2. Sailpoint
    3. Salesforce
    4. Oracle
    5. SQL

I need them to pull out Data referencing the Users name. For example

  • So if Column A = Jayne
  • Then check 'Data Sheet' Table A:C
  • Search for System Name 'Active Directory' in Column C of the Data Sheet
  • If the system name exist, spit out answer YES, otherwise NO

 

My experience is limited to Vlookups and I have tried using that but struggling. I hope someone can help me please :)

 

Cheers, Adrien

 

 

 

 

 

 

 

4 Replies

@aricoux 

 

Try to use this forum in B2

=IF(ISNUMBER(FIND("active directory",Table1[@System]))=TRUE,"Yes","No"), 

and do the same for all systems

Thanks @Jihad Al-Jarady it works great, however, is there a way to include that FORMULA including referencing the person's name as an additional lookup? The only reason for that is to ensure that the systems reflect the person's name and not purely based on how the data is laid out in the first Table :) 

@aricoux I think this is what you are looking for:

=IF(SUMPRODUCT(($A2=Data!$B:$B)*ISNUMBER(SEARCH(B$1,Data!$C:$C))),"Yes","No")

@mtarler You are a champion! Just tested and works like a dream. Cannot wait to use it tomorrow on the real thing! 

 

Thanks so much, everyone for the quick responses and for helping me out! I will be back again I am sure :)