... The formulas above only use COLUMN to AI where as the TestYear data set goes to AN. Adjusting the formula to suit results in #N/A. Is this coincidental as the 5 missing columns = 5 starting columns before the years (A:E vs AJ:AN)?

TextYear is an array spanning columns 6 - 40. However, the elements of that array are accessed by addresses 1 - 35.

The IF condition test returns an array of TRUE or FALSE values depending on the result of the test in each column: {TRUE,FALSE,FALSE,TRUE,FALSE,...,FALSE}.

Note that the elements of the array are counted starting from 1 - 35. In order to get the correct element from the array, I used COLUMNS(A1:AI1) which returns the array {1,2,...,35}. For the example sheet, the array passed to MAX() from the IF() is {1,FALSE,FALSE,4,FALSE,...,FALSE}. MAX() returns 4 to INDEX(), INDEX() returns the forth element of TestYear: 2021.

If you use COLUMNS(F1:AN1), the array would be {6,7,8,...,40}; the IF() would then return {6,FALSE,FALSE,9,...,FALSE} to MAX() which would return 9 to INDEX(), i.e., 2026
COLUMNS(F1:AN1)-5 is another alternative.