Forum Discussion

Aaron04's avatar
Aaron04
Occasional Reader
Feb 13, 2026

Formual Error

Trying to create and excel sheet that shows the top 3 tour members for each month of the year but the formula i'm using to show me that information is showing up as an error and i'm confused as to wh...
excel
Olufemi7's avatar
Olufemi7
Iron Contributor
Feb 14, 2026

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.