## Max Min Match

I have a spreadsheet where I have been tracking the daily prices of my stocks for over 5 years. I recently decided that I wanted to know when my portfolio had peaked and hit its low.

Using INDEX, MATCH, MIN, and MAX, I get the amounts of the peaks and valleys and the dates.

```

Max
Min

Max Date
Min Date

\$40,616.50
\$35,180.70
Value
11/24/2004
09/20/2001

11,313.51
7,286.27
DJIA
08/02/2000
09/05/2002

4,274.67
1,114.09
NASDAQ
06/08/2000
09/05/2002

1,184.17
993.70
S&P500
10/05/2004
07/02/2003

The numbers (my portfolio values have been altered).

Max
Min

=MAX(B2:B1000)
=MIN(B2:B1000)
Value

=MAX(C27:C1000)
=MIN(C27:C1000)
DJIA

=MAX(D27:D1000)
=MIN(D27:D1000)
NASDAQ

=MAX(E28:E1001)
=MIN(E28:E1001)
S&P500

MINimum and MAXimum

Max Date

=INDEX(\$A\$1:\$A\$1065,MATCH(MAX(\$B\$2:\$B1065),\$B\$2:\$B\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MAX(\$C\$27:\$C1066),\$C\$27:\$C\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MAX(\$D\$27:\$D1067),\$D\$27:\$D\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MAX(\$E\$27:\$E1068),\$E\$27:\$E\$1065,0))

MAX Date

Min Date

=INDEX(\$A\$1:\$A\$1065,MATCH(MIN(\$B\$2:\$B1065),\$B\$2:\$B\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MIN(\$C\$27:\$C1066),\$C\$27:\$C\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MIN(\$D\$27:\$D1067),\$D\$27:\$D\$1065,0))

=INDEX(\$A\$1:\$A\$1065,MATCH(MIN(\$E\$27:\$E1068),\$E\$27:\$E\$1065,0))

MIN Date
```

INDEX is used to determine what row the MAX or MIN occurs. MATCH is used to