Dec 16 2020 10:44 AM
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.
I need them to pull out Data referencing the Users name. For example
My experience is limited to Vlookups and I have tried using that but struggling. I hope someone can help me please :)
Cheers, Adrien
Dec 16 2020 11:43 AM
Try to use this forum in B2
=IF(ISNUMBER(FIND("active directory",Table1[@System]))=TRUE,"Yes","No"),
and do the same for all systems
Dec 16 2020 12:15 PM
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 :)
Dec 16 2020 12:20 PM
@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")
Dec 16 2020 01:46 PM
@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 :)