Given a similar setup in A1:
<STYLE>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl66 { color: windowtext; font-size: 11pt; font-family: Calibri; text-align: left; }.xl67 { font-size: 11pt; text-align: center; vertical-align: middle; }.xl68 { }</STYLE>
| Jan-10
| Feb-10
| Mar-10
| Apr-10
| May-10
| Jun-10
| Jul-10
| Aug-10
| Sep-10
| Oct-10
|
THIS
| 28.17%
| | 30.55%
| 33.36%
| | 28.92%
| | 16.69%
| 18.53%
| 18.22%
|
THAT
| 19.09%
| | 13.65%
| 14.04%
| | 19.25%
| | 16.07%
| 19.24%
| 23.73%
|
THOSE
| 8.01%
| | 4.92%
| 6.62%
| | 6.82%
| | 4.88%
| 4.09%
| 10.85%
|
THESE
| 0.22%
| | 0.19%
| 0.34%
| | 0.37%
| | 0.31%
| 0.07%
| 0.39%
|
ALSO
| 29.88%
| | 14.09%
| 19.64%
| | 29.45%
| | 21.82%
| 26.22%
| 31.54%
|
AND
| 3.15%
| | 2.44%
| 3.41%
| | 5.84%
| | 25.14%
| 15.40%
| 12.25%
|
<TBODY>
</TBODY>
A formula returns the MAX and the MIN for each category, example for 'THIS' MAX is 33.36% in Apr-10 and the MIN is 16.69% in Aug-10
Cell A9 returns the category (THIS, THAT...) and cell B9 returns the MAX or MIN.
The formula is =LOOKUP(B9,INDEX($B$2:$K$7,MATCH(A9,$A$2:$A$7,0),0),$B$1:$K$1) and does NOT return the correct date.
<STYLE>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl66 { color: windowtext; font-size: 11pt; font-family: Calibri; }.xl67 { color: windowtext; font-size: 11pt; font-family: Calibri; text-align: left; }.xl68 { color: windowtext; font-size: 11pt; font-family: Calibri; }</STYLE>
THIS
| 33.36%
| Oct-10
|
THIS
| 16.69%
| #N/A
|
<TBODY>
</TBODY>
QUESTION: Is it because of the MAX() or MIN() in column B?
What should be modified in the formula to return the correct date?