Forum Discussion

aricoux's avatar
aricoux
Copper Contributor
Dec 16, 2020

Simple Systems access matrix I need a formula! Please help

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

 

 

 

 

 

 

 

BI & Data Analysis
excel
Formulas and Functions

4 Replies

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    MCT
    Dec 16, 2020

    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

    • aricoux's avatar
      aricoux
      Copper Contributor
      Dec 16, 2020

      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 🙂 

Resources