looking for kind a vlookup with multiple search and compare criteria

How are you storing your data, which matches rights to users?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Doesn't help much - are they stored as boolean values / numbers / text, in multiple columns / single column / comma delimited... what?

An example workbook would really help.

Okay - this probably isn't the most efficient, but it works on your sample workbook:

Sub GetRoles()
    Dim lRApp As Long
    Dim lRUser As Long
    Dim lRRole As Long
    Dim lRRoleVal As Long
    
    Worksheets("EXPECTED RESULT").Range("A2").CurrentRegion.Offset(1, 0).Clear
    
    With Worksheets("INPUT_APP")
        lRApp = .Cells(Rows.Count, 1).End(xlUp).Row - 1
        With .Range("E2").Resize(lRApp, 1)
            .FormulaR1C1 = "=EnumRights(RC[-1])"
            .Value = .Value
        End With
        .Range("A2").Resize(lRApp, 3).Copy
    End With
    
    With Worksheets("EXPECTED RESULT")
        .Range("A2").PasteSpecial Paste:=xlPasteValues
        .Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
        lRUser = .Cells(Rows.Count, 1).End(xlUp).Row - 1
        .Range("E2").Resize(lRUser, 1).FormulaR1C1 = "=SUMIFS(INPUT_APP!R2C5:R" & lRApp + 1 & "C5,INPUT_APP!R2C1:R" & lRApp + 1 & "C1,RC1,INPUT_APP!R2C3:R" & lRApp + 1 & "C3,RC3)"
    End With
    
    With Worksheets("INPUT_ROLE")
        lRRole = .Cells(Rows.Count, 1).End(xlUp).Row - 1
        With .Range("D2").Resize(lRRole, 1)
            .FormulaR1C1 = "=EnumRights(RC[-1])"
            .Value = .Value
        End With
        .Range("A2").Resize(lRRole, 2).Copy
    End With
    
    With Worksheets("EXPECTED RESULT")
        .Range("G2").PasteSpecial Paste:=xlPasteValues
        .Range("G2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
        lRRoleVal = .Cells(Rows.Count, 7).End(xlUp).Row - 1
        .Range("I2").Resize(lRRoleVal, 1).FormulaR1C1 = "=RC7&""-""&SUMIFS(INPUT_ROLE!R2C4:R" & lRRole + 1 & "C4,INPUT_ROLE!R2C1:R" & lRRole + 1 & "C1,RC7,INPUT_ROLE!R2C2:R" & lRRole + 1 & "C2,RC8)"
    End With
    
    With Worksheets("EXPECTED RESULT")
        With .Range("D2").Resize(lRUser, 1)
            .FormulaR1C1 = "=INDEX(R2C8:R" & lRRoleVal + 1 & "C8,MATCH(RC3&""-""&RC5,R2C9:R" & lRRoleVal + 1 & "C9,0))"
            .Value = .Value
        End With
        .Range("E2").Resize(Application.Max(lRUser, lRRoleVal), 5).Clear
        Application.Goto .Range("A1"), True
    End With
    
    Worksheets("INPUT_ROLE").Range("D2").Resize(lRRole, 1).Clear
    Worksheets("INPUT_APP").Range("E2").Resize(lRApp, 1).Clear
    
End Sub

Function EnumRights(ByVal s As String)
    Select Case UCase(s):
        Case "READ":        EnumRights = 2 ^ 0
        Case "WRITE":       EnumRights = 2 ^ 1
        Case "EXECUTE":     EnumRights = 2 ^ 2
        Case "DELETE":      EnumRights = 2 ^ 3
        Case Else:          EnumRights = 0
    End Select
End Function