Formual Error

This reply was based upon a copy of the IlirU dataset and generates the result crosstab shown in yellow.

= LET(
    TOP3λ,    LAMBDA(s,t,IF(@s>=LARGE(t,3), @s, 0)),
    crosstab, PIVOTBY(Attendance, DATEVALUE("1/"&Month), Point, TOP3λ,,0,,0),
    nonblank, BYROW(DROP(crosstab, , 1), OR),
    FILTER(crosstab, nonblank)
  )

The Lambda function TOP3λ returns a scalar '@s' if it is one of the top 3 values within the overall list of points 't' for the month, 0 otherwise.  Blank rows are then filtered out.

Aaron04​ ,

(see the screenshot above)

Apply in cell E1 below formula:

=LET(
mth, A2:A21, att, B2:B21, pt, C2:C21,
t, LET(wr, WRAPROWS(TEXTSPLIT(ARRAYTOTEXT(BYROW(UNIQUE(mth),
LAMBDA(a, LET(sr, SORT(FILTER(HSTACK(mth, att, pt),
IFERROR(SEQUENCE(ROWS(mth)) / (mth = a), FALSE)), 2, -1),
ARRAYTOTEXT(FILTER(sr, BYROW(TAKE(sr,, -1) = LARGE(TAKE(sr,, -1), {1,2,3}), OR))))))),, ", "), 3),
IF(ISNUMBER(--wr), --wr, wr)), chc, CHOOSECOLS(t, 1),
VSTACK(HSTACK("Name", TOROW(TEXT(UNIQUE(MONTH(chc & 1)) * 28, "mmm"))),
DROP(PIVOTBY(CHOOSECOLS(t, 2), MONTH(chc & 1), CHOOSECOLS(t, 3), SINGLE,, 0,, 0), 1))
)

or apply in cell K1 below formula:

=LET(
mth, A2:A21, umth, MONTH(mth & 1), unq, UNIQUE(mth),
VSTACK(HSTACK("Month", "3 best Attendance"),
HSTACK(unq, BYROW(BYROW(SEQUENCE(ROWS(unq)),
LAMBDA(a, ARRAYTOTEXT(CHOOSECOLS(SORT(VSTACK(TOROW(CHOOSEROWS(TEXTSPLIT(TEXTJOIN(";",,
DROP(GROUPBY(umth, B2:B21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3),
TOROW(CHOOSEROWS(--TEXTSPLIT(TEXTJOIN(";",,
DROP(GROUPBY(umth, C2:C21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3)), 2, -1, TRUE), {1,2,3})))),
LAMBDA(b, TEXTBEFORE(b, ",", 3)))))
)

Let me know if these formulas worked for you or not. If they work then please mark my reply as a Valid Answer and kindly upvote it.

IlirU

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}"