Formual Error

HelloAaron04​,

The formula is giving an error because the @ operator cannot be used inside array calculations. It forces a single-value reference, but multiplying logical arrays requires full array evaluation.

Corrected formula:

=INDEX('Tour Attendance Tracker'!C2:C500, MATCH(1, ('Tour Attendance Tracker'!A2:A500=A2)*('Tour Attendance Tracker'!F2:F500=C2), 0))

In older Excel (pre-365), press Ctrl + Shift + Enter to confirm as an array formula.

Note: This only returns the first match. To get the top 3 tour members per month, use a Pivot Table with a Top 3 filter or, in Excel 365/2021, a dynamic array formula:

=TAKE(SORT(FILTER('Tour Attendance Tracker'!C2:F500, 'Tour Attendance Tracker'!A2:A500=A2), 4, -1), 3)

This will return the top 3 members based on attendance.

I don't know what those columns are but I'm guessing the problem is with the @ symbols.  That symbol will take only the corresponding line item from the array so you are probably getting a MATCH finding no result and hence the error.

You can/should also consider using XLOOKUP for this

Also this would appear to only return 1 value not the top 3, but I'm guessing you know that and have a similar formula for the other 2?

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Power Query M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Month"},  
                     {"GetandTransform", (t)=>
                                     let   sort = Table.Sort(t,{"Value", Order.Descending}),
                                           AddedIndex=Table.AddIndexColumn(sort,"Index",1),
                                           SelectRows = Table.SelectRows(AddedIndex, each [Index] < 4)
                                     in    SelectRows }),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "GetandTransform", {"City", "Value",  "Index"}, {"City", "Value",  "Index"})
in
    #"Expanded {0}"