Comparison of row with a column (named range)

Question

My data:

TABLE 1	Person1	Person2	Person3	Person4	Person5	Person6	Person7	Person8
Monday	A	B	C	D	E	E	F	F
Tuesday	B	F	D	C	E	E	F	A
Wednesday	C	D	A	F	E	E	F	F

PLACE
A
B
C
D

My question: I want to compare if on each day all the values from the named range Place are within the row of Monday, Tuesday, Wednesday,... and if some are missing which one these are.
So, for Wednesday it should give a "B" missing withing the IF formula or whatever suggestion you have. "E" and "F" are not in Place, those should be ignored.

What I tried so far: Research always led me to comparison of 2 columns instead of rows&columns.
I tried array functions like this one ($B3:$I3 == "Monday row" == "row1"):

=INDEX(Place;SMALL(IF(ISERROR(MATCH(Place;$B3:$I3;0));(ROW(Place)-MIN(ROW(Place))+1);"");ROWS($A$1:A1)))

Error: Many "helper" columns needed for inidividual rows to compare. Also "#NUM!" values even if all vlaues from Place occur or just one is missing.

I got startet with many COUNTIF formulas like

=COUNTIF(row1;INDEX(Place;1))+COUNTIF(row1;INDEX(Place;2))+COUNTIF(row1;INDEX(Place;3))+COUNTIF(row1;INDEX(Place;4))

giving me a "4" when all values occur and lower values if some or all are missing. But how can I connect getting these number to "seeing" which values are missing to cause the "lower than 4" number?

Answer 1

I think this UDF will give you the result you want.

Function Check(Data As Range) As String
    ' 296
    
    ' Data specifies one row from the table excl column #1
    Dim Fun()       As String               ' function return value
    Dim i           As Long                 ' index to Fun()
    Dim Places      As Variant              ' array of range "Place"
    Dim R           As Long                 ' loop counter; Places
    Dim Match       As Variant              ' result of Match() function
    
    ' this command will slow down your workbook.
    '   If inconvenient (too sluggish), remove it.
    ' If removed, the UDFs in the worksheet may not recalculate as you expect.
    '   Click "Calculate Now" on the ribbon's 'Formulas' tab to update.
    Application.Volatile
    
    ' the named range "Places" must exist in the worksheet
    ' in the sample it's B10:B13
    Places = ActiveSheet.Range("Places").Value
    ReDim Fun(UBound(Places))
    
    For R = 1 To UBound(Places)
        Match = Application.Match(Places(R, 1), Data, 0)
        If IsError(Match) Then
            i = i + 1
            Fun(i) = Places(R, 1)
        End If
    Next R
    
    If i Then
        Fun(0) = "Missing: "
    Else
        Fun(0) = "All present"
    End If
    ReDim Preserve Fun(i)
    Check = Replace(Join(Fun, ", "), ",", vbNullString, 1, 1)
End Function

The code must be pasted to a standard code module. You have to insert it, and it will have a name like "Module1" by default, which you can change. Don't use any of the existing code modules. Call the function from anywhere in the workbook by entering syntax similar to this: =Check(C3:J3). Here C3:J3 defines the 2nd to last columns of your table, excluding "Monday", and the result will evaluate items listed in this range against the range Places.