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 Loo...
BI & Data Analysis
excel
Formulas and Functions
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 🙂 

mtarler's avatar
mtarler
Silver Contributor
Dec 16, 2020

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")
  • aricoux's avatar
    aricoux
    Copper Contributor
    Dec 16, 2020

    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 🙂

     

Resources