Trying to get rid of #N/A when using Index and Match in a Matrix

I am trying to get rid of #N/A when using Index and Match in a matrix. I used the following equation...

=IF(ISERROR(INDEX($A$35:$A$205,MATCH(MAX(G$35:G$205),(G$35:G$205),0))),0,INDEX($A$35:$A$205,MATCH(MAX(G$35:G$205),(G$35:G$205),0)))

However now I am getting just 0 instead of the cell it is referencing. I did verify it by clicking Ctrl Shift & enter simultaneously.

Thanks in advance for any help on this!

Unfortunately, I am still getting a 0.

The value returned should be 1964. Here is a sample of the spreadsheet.

1964 9 7 82 63 72.5 2.39
1965 9 6 58 48 53.0 0.20
1966 9 5 67 47 57.0 0.00
1967 9 4 75 45 60.0 0.00
1968 9 2 74 40 57.0 0.00
1969 9 1 75 46 60.5 0.00
1970 9 7 84 63 73.5 0.00
1971 9 6 77 57 67.0 T
1972 9 4 72 47 59.5 T
1973 9 3 78 64 71.0 0.51
1974 9 2 63 37 50.0 T
1975 9 1 80 62 71.0 T
1976 9 6 85 58 71.5 0.00
1977 9 5 78 56 67.0 0.00
1978 9 4 85 58 71.5 0.00
1979 9 3 77 53 65.0 0.00
1980 9 1 76 55 65.5 T
1981 9 7 69 53 61.0 0.35
1982 9 6 64 52 58.0 T
1983 9 5 72 63 67.5 0.14
1984 9 3 70 47 58.5 T
1985 9 2 78 55 66.5 0.11
1986 9 1 80 57 68.5 0.00
1987 9 7 76 53 64.5 0.31
1988 9 5 65 39 52.0 0.00
1989 9 4 77 55 66.0 0.31
1990 9 3 80 62 71.0 0.78
1991 9 2 83 56 69.5 0.00
1992 9 7 74 49 61.5 0.10
1993 9 6 68 46 57.0 0.00
1994 9 5 75 53 64.0 0.05
1995 9 4 83 58 70.5 0.00
1996 9 2 79 64 71.5 0.03
1997 9 1 82 62 72.0 0.05
1998 9 7 69 50 59.5 0.00
1999 9 6 73 47 60.0 0.00
2000 9 4 64 49 56.5 0.00
2001 9 3 79 58 68.5 0.00
2002 9 2 84 56 70.0 0.00
2003 9 1 77 52 64.5 0.00
2004 9 6 73 50 61.5 0.01
2005 9 5 85 63 74.0 0.00
2006 9 4 73 57 65.0 0.01
2007 9 3 86 59 72.5 0.00
2008 9 1 85 66 75.5 0.00
2009 9 7 77 51 64.0 0.00
2010 9 6 72 56 64.0 0.26
2011 9 5 68 43 55.5 0.00
2012 9 3 80 66 73.0 0.07
2013 9 2 64 49 56.5 0.00
2014 9 1
2015 9 7
2016 9 5
2017 9 4
2018 9 3
2019 9 2
2020 9 7

Averages 76 54 64.9 0.11

Maximum 98 72 82.5 2.39
Year 1913 1960 1960 0
Occurrences 1 1 1 1

Here is a sample. It seems like when there are #N/A in the column the value goes to zero.

Originally Posted by daddylonglegs

You could also use AGGREGATE function to get the MAX value like this

=AGGREGATE(4,6,D2:D14)

4 = Max
6 = Ignore errors

Never heard of this function. I tried it a couple of times and for some reason it kept locking up my spreadsheet. Will have to try it on another one sometime.

This worked out great! Thanks a lot!

Originally Posted by ConneXionLost

Since you're already looking up the Max in a seperate cell, try this.

Thanks a lot! This worked great!