Search along row and paste into destination cell

run in attached workbook with {CTRL} t

Provided that sheet has same structure, this will work for as many tables as you want

VBA
- finds value "Applicable Tables" in row 1 to determine last column
- loop through all rows with value in cell in column A (starting at row 3)
- checks if "Yes" in any column
- checks if value in cell row 2 = "Applicable"
- adds value of cell in row 1 to the destination cell

Sub tiubacker()
    Dim ws As Worksheet, rng As Range, cel As Range, target As Range, check As Range
    Dim lastCol As Long, r As Long
    Set ws = Sheets("Data")
     
With ws
    Set rng = .Range("A3", .Range("A" & Cells.Rows.Count).End(xlUp))
    
    lastCol = .Rows("1:1").Find("Applicable Tables", LookIn:=xlFormulas).Column
    
    For Each cel In rng
        Set target = .Cells(cel.Row, lastCol)
        r = cel.Row
        target.Value = ""
        For c = 2 To lastCol
            Set check = .Cells(r, c)
            If check.Value = "Yes" And .Cells(2, c) = "Applicable" Then
                If target.Value = "" Then
                    target.Value = .Cells(1, c).Value
                Else
                    target.Value = target.Value & Chr(10) & .Cells(1, c).Value
                End If
            End If
        Next c
    Next cel
    
End With
    
End Sub

UDF = user defined function
- which is VBA code that can be used as a regular formula in your worksheet

perhaps something like this:
=ApplicableTables(C3,E3,G3,I3) in cell K3 and copy down

The VBA would then put the relevant table names in each cell