One of the reports that wastes a bunch of my time at work is the Roster. It's a multi-site, multi-contract listing of every employee currently assigned to a specific client. Currently, it has a little over 6,000 lines by 20-something columns, indexed against 3 different datasets. Not the largest mess in the world, but still a pain. And it's almost all in excel, because I somehow don't have a business case for Access.

But one part of this monster stands apart. One tab per site `Site Totals`, listing off every time any agent has gone through training. A second tab (again, one per site) `Site Data` displaying only the most recent training class, and the credentials they had during that class.

That second tab is driven by variations of this array formula - Last\_Row is a named range on another tab, and column A is a pivot of the UID column on `Site Totals`. I've broken it apart for readability:

```
=IF(INDEX('Site Totals'!B:B,LARGE(($A2=INDIRECT("'Site Totals'!$A$1:$A$"&Last_Row))*
 (INDIRECT("'Site Totals'!B1:B"&Last_Row)<>"")*
 ROW(INDIRECT("'Site Totals'!$A$1:$A$"&Last_Row)),1))="Trainer",
 "",
 INDEX('Site Totals'!B:B,LARGE(($A2=INDIRECT("'Site Totals'!$A$1:$A$"&Last_Row))*
 (INDIRECT("'Site Totals'!B1:B"&Last_Row)<>"")*
 ROW(INDIRECT("'Site Totals'!$A$1:$A$"&Last_Row)),1)))

```

I know what this formula does, but I don't know how to improve it. This formula needs to be changed, because it currently is on the order of 500 Million calculations (I'm not allowed to delete historical data), and it takes me 3 hours to calculate the workbook ... if it doesn't crash Excel first.

I'm open to VBA and / or custom functions, but would prefer to have native Excel functions. I'm not able to install anything, so any solution must be native Excel, and Must be compatible to Excel 2007.