Formula to return column header

[btadams]

I have a table of data and need a formula to find the minimum in that row and return the text that appears in that column's header. For example say the min occurs in the 3rd column of a named range, I want the text that appears in the first cell of that 3rd column. Thanks,

 

[cyrilbrd]

Thanks , the first returns the correct query but the second returned only one occurrence, while cdt1 Nicola, cdt2 S cdt3 T should return two occurrences, namely Week1 and Week3... right?

 

[Aladin Akyurek]

Thanks , the first returns the correct query but the second returned only one occurrence, while cdt1 Nicola, cdt2 S cdt3 T should return two occurrences, namely Week1 and Week3... right?

Nicola has just 1 S. Her second recod is an N. If you change that N to S, you'll get week 3 also.

 

[cyrilbrd]

Nicola has just 1 S. Her second recod is an N. If you change that N to S, you'll get week 3 also.

mmmh, what I meant is:

<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; }.xl64 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; white-space: normal; }.xl65 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(218, 238, 243); white-space: normal; }.xl66 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 233, 217); white-space: normal; }.xl67 { text-align: center; border: 0.5pt solid windowtext; }.xl68 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(228, 223, 236); white-space: normal; }.xl69 { font-weight: 700; text-decoration: underline; font-family: Calibri; vertical-align: middle; white-space: normal; }</style> [TABLE="width: 390"]
<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 65"]cdt1
[/TD]
[TD="class: xl64, width: 65"]cdt2
[/TD]
[TD="class: xl65, width: 65"]Week 1
[/TD]
[TD="class: xl65, width: 65"]Week 2[/TD]
[TD="class: xl65, width: 65"]Week 3
[/TD]
[TD="class: xl65, width: 65"]Week 4[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 65"]Nicola
[/TD]
[TD="class: xl68, width: 65"]S
[/TD]
[TD="class: xl67"]T
[/TD]
[TD="class: xl67"]V[/TD]
[TD="class: xl67"]T
[/TD]
[TD="class: xl67"]D[/TD]
[/TR]
</tbody>[/TABLE]

cdt1 Nicola
cdt2 S
cdt3 T (two T within cdt1 and cdt2)
result expected is week1 and week3
does this clarifies my query?

 

[Aladin Akyurek]

mmmh, what I meant is:

<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; }.xl64 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; white-space: normal; }.xl65 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(218, 238, 243); white-space: normal; }.xl66 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 233, 217); white-space: normal; }.xl67 { text-align: center; border: 0.5pt solid windowtext; }.xl68 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(228, 223, 236); white-space: normal; }.xl69 { font-weight: 700; text-decoration: underline; font-family: Calibri; vertical-align: middle; white-space: normal; }</STYLE>[TABLE="width: 390"]
<TBODY>[TR]
[TD="class: xl69, width: 65"]cdt1
[/TD]
[TD="class: xl64, width: 65"]cdt2
[/TD]
[TD="class: xl65, width: 65"]Week 1
[/TD]
[TD="class: xl65, width: 65"]Week 2
[/TD]
[TD="class: xl65, width: 65"]Week 3
[/TD]
[TD="class: xl65, width: 65"]Week 4
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 65"]Nicola
[/TD]
[TD="class: xl68, width: 65"]S
[/TD]
[TD="class: xl67"]T
[/TD]
[TD="class: xl67"]V
[/TD]
[TD="class: xl67"]T
[/TD]
[TD="class: xl67"]D
[/TD]
[/TR]
</TBODY>[/TABLE]

cdt1 Nicola
cdt2 S
cdt3 T (two T within cdt1 and cdt2)
result expected is week1 and week3
does this clarifies my query?

Apparently, I overlooked the different week occurrences in the second case...

[TABLE="width: 640"]
<TBODY>[TR]
[TD="class: xl64, width: 97, bgcolor: white"][/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Rank
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]Week 1
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]Week 2
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]Week 3
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]Week 4
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 70, bgcolor: white"]cdt1
[/TD]
[TD="class: xl64, width: 80, bgcolor: white"]Nicola
[/TD]
[TD="class: xl64, width: 75, bgcolor: white"][/TD]
[TD="class: xl64, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Nicola
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]S
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 70, bgcolor: white"]cdt2
[/TD]
[TD="class: xl64, width: 80, bgcolor: white"]S
[/TD]
[TD="class: xl64, width: 75, bgcolor: white"][/TD]
[TD="class: xl64, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Kevin
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]P
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 70, bgcolor: white"]cdt3
[/TD]
[TD="class: xl64, width: 80, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 75, bgcolor: white"][/TD]
[TD="class: xl64, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Nicola
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]N
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]V
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 70, bgcolor: white"]count
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Sam
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]S
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]T
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 70, bgcolor: white"]result
[/TD]
[TD="class: xl63, bgcolor: transparent"]Week 1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Week 3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]P
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Roger
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]V
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Kevin
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]A
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]D
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Michael
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Q
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]T
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]V
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]W
[/TD]
[TD="class: xl64, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 88, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 87, bgcolor: white"]V
[/TD]
[TD="class: xl64, width: 81, bgcolor: white"]V
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

I4, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$11=$I$1,IF($B$2:$B$11=$I$2,
  IF($C$2:$F$11=$I$3,COLUMN($C$1:$F$1)))),COLUMN($C$1:$F$1)),1))

I5, control+shift+enter and copy across:

=IF(COLUMNS($I$5:I5)<=$I$4,INDEX($C$1:$F$1,MIN(IF($A$2:$A$11=$I$1,
  IF($B$2:$B$11=$I$2,IF($C$2:$F$11=$I$3,IF(ISNA(MATCH($C$1:$F$1,$H$5:H5,0)),
  COLUMN($C$1:$F$1)-COLUMN($C$1)+1)))))),"")

 

[cyrilbrd]

=SUM(IF(FREQUENCY(IF($A$2:$A$11=$I$1,IF($B$2:$B$11=$I$2,
  IF($C$2:$F$11=$I$3,COLUMN($C$1:$F$1)))),COLUMN($C$1:$F$1)),1))

=IF(COLUMNS($I$5:I5)<=$I$4,INDEX($C$1:$F$1,MIN(IF($A$2:$A$11=$I$1,
  IF($B$2:$B$11=$I$2,IF($C$2:$F$11=$I$3,IF(ISNA(MATCH($C$1:$F$1,$H$5:H5,0)),
  COLUMN($C$1:$F$1)-COLUMN($C$1)+1)))))),"")

Thanks Aladin, sorry for late reply.
It works perfectly.

 

[Aladin Akyurek]

Thanks Aladin, sorry for late reply.
It works perfectly.

Thank you. Nice questions.

 

[cyrilbrd]

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> [TABLE="class: grid, width: 715"]
<tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]Jan-10[/TD]
[TD="width: 65"]Feb-10[/TD]
[TD="width: 65"]Mar-10[/TD]
[TD="width: 65"]Apr-10[/TD]
[TD="width: 65"]May-10[/TD]
[TD="width: 65"]Jun-10[/TD]
[TD="width: 65"]Jul-10[/TD]
[TD="width: 65"]Aug-10[/TD]
[TD="width: 65"]Sep-10[/TD]
[TD="width: 65"]Oct-10[/TD]
[/TR]
[TR]
[TD]THIS
[/TD]
[TD="align: right"]28.17%[/TD]
[TD][/TD]
[TD="align: right"]30.55%[/TD]
[TD="align: right"]33.36%[/TD]
[TD][/TD]
[TD="align: right"]28.92%[/TD]
[TD][/TD]
[TD="align: right"]16.69%[/TD]
[TD="align: right"]18.53%[/TD]
[TD="align: right"]18.22%[/TD]
[/TR]
[TR]
[TD]THAT
[/TD]
[TD="align: right"]19.09%[/TD]
[TD][/TD]
[TD="align: right"]13.65%[/TD]
[TD="align: right"]14.04%[/TD]
[TD][/TD]
[TD="align: right"]19.25%[/TD]
[TD][/TD]
[TD="align: right"]16.07%[/TD]
[TD="align: right"]19.24%[/TD]
[TD="align: right"]23.73%[/TD]
[/TR]
[TR]
[TD]THOSE
[/TD]
[TD="align: right"]8.01%[/TD]
[TD][/TD]
[TD="align: right"]4.92%[/TD]
[TD="align: right"]6.62%[/TD]
[TD][/TD]
[TD="align: right"]6.82%[/TD]
[TD][/TD]
[TD="align: right"]4.88%[/TD]
[TD="align: right"]4.09%[/TD]
[TD="align: right"]10.85%[/TD]
[/TR]
[TR]
[TD]THESE
[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD="align: right"]0.19%[/TD]
[TD="align: right"]0.34%[/TD]
[TD][/TD]
[TD="align: right"]0.37%[/TD]
[TD][/TD]
[TD="align: right"]0.31%[/TD]
[TD="align: right"]0.07%[/TD]
[TD="align: right"]0.39%[/TD]
[/TR]
[TR]
[TD]ALSO
[/TD]
[TD="align: right"]29.88%[/TD]
[TD][/TD]
[TD="align: right"]14.09%[/TD]
[TD="align: right"]19.64%[/TD]
[TD][/TD]
[TD="align: right"]29.45%[/TD]
[TD][/TD]
[TD="align: right"]21.82%[/TD]
[TD="align: right"]26.22%[/TD]
[TD="align: right"]31.54%[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[TD="align: right"]3.15%[/TD]
[TD][/TD]
[TD="align: right"]2.44%[/TD]
[TD="align: right"]3.41%[/TD]
[TD][/TD]
[TD="align: right"]5.84%[/TD]
[TD][/TD]
[TD="align: right"]25.14%[/TD]
[TD="align: right"]15.40%[/TD]
[TD="align: right"]12.25%[/TD]
[/TR]
</tbody>[/TABLE]



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> [TABLE="class: grid, width: 195"]
<tbody>[TR]
[TD="width: 65"]THIS
[/TD]
[TD="width: 65"]33.36%[/TD]
[TD="width: 65, align: right"]Oct-10[/TD]
[/TR]
[TR]
[TD]THIS
[/TD]
[TD]16.69%[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


QUESTION: Is it because of the MAX() or MIN() in column B?
What should be modified in the formula to return the correct date?

 

[Aladin Akyurek]

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>[TABLE="class: grid, width: 715"]
<TBODY>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]Jan-10
[/TD]
[TD="width: 65"]Feb-10
[/TD]
[TD="width: 65"]Mar-10
[/TD]
[TD="width: 65"]Apr-10
[/TD]
[TD="width: 65"]May-10
[/TD]
[TD="width: 65"]Jun-10
[/TD]
[TD="width: 65"]Jul-10
[/TD]
[TD="width: 65"]Aug-10
[/TD]
[TD="width: 65"]Sep-10
[/TD]
[TD="width: 65"]Oct-10
[/TD]
[/TR]
[TR]
[TD]THIS
[/TD]
[TD="align: right"]28.17%
[/TD]
[TD][/TD]
[TD="align: right"]30.55%
[/TD]
[TD="align: right"]33.36%
[/TD]
[TD][/TD]
[TD="align: right"]28.92%
[/TD]
[TD][/TD]
[TD="align: right"]16.69%
[/TD]
[TD="align: right"]18.53%
[/TD]
[TD="align: right"]18.22%
[/TD]
[/TR]
[TR]
[TD]THAT
[/TD]
[TD="align: right"]19.09%
[/TD]
[TD][/TD]
[TD="align: right"]13.65%
[/TD]
[TD="align: right"]14.04%
[/TD]
[TD][/TD]
[TD="align: right"]19.25%
[/TD]
[TD][/TD]
[TD="align: right"]16.07%
[/TD]
[TD="align: right"]19.24%
[/TD]
[TD="align: right"]23.73%
[/TD]
[/TR]
[TR]
[TD]THOSE
[/TD]
[TD="align: right"]8.01%
[/TD]
[TD][/TD]
[TD="align: right"]4.92%
[/TD]
[TD="align: right"]6.62%
[/TD]
[TD][/TD]
[TD="align: right"]6.82%
[/TD]
[TD][/TD]
[TD="align: right"]4.88%
[/TD]
[TD="align: right"]4.09%
[/TD]
[TD="align: right"]10.85%
[/TD]
[/TR]
[TR]
[TD]THESE
[/TD]
[TD="align: right"]0.22%
[/TD]
[TD][/TD]
[TD="align: right"]0.19%
[/TD]
[TD="align: right"]0.34%
[/TD]
[TD][/TD]
[TD="align: right"]0.37%
[/TD]
[TD][/TD]
[TD="align: right"]0.31%
[/TD]
[TD="align: right"]0.07%
[/TD]
[TD="align: right"]0.39%
[/TD]
[/TR]
[TR]
[TD]ALSO
[/TD]
[TD="align: right"]29.88%
[/TD]
[TD][/TD]
[TD="align: right"]14.09%
[/TD]
[TD="align: right"]19.64%
[/TD]
[TD][/TD]
[TD="align: right"]29.45%
[/TD]
[TD][/TD]
[TD="align: right"]21.82%
[/TD]
[TD="align: right"]26.22%
[/TD]
[TD="align: right"]31.54%
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[TD="align: right"]3.15%
[/TD]
[TD][/TD]
[TD="align: right"]2.44%
[/TD]
[TD="align: right"]3.41%
[/TD]
[TD][/TD]
[TD="align: right"]5.84%
[/TD]
[TD][/TD]
[TD="align: right"]25.14%
[/TD]
[TD="align: right"]15.40%
[/TD]
[TD="align: right"]12.25%
[/TD]
[/TR]
</TBODY>[/TABLE]



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>[TABLE="class: grid, width: 195"]
<TBODY>[TR]
[TD="width: 65"]THIS
[/TD]
[TD="width: 65"]33.36%
[/TD]
[TD="width: 65, align: right"]Oct-10
[/TD]
[/TR]
[TR]
[TD]THIS
[/TD]
[TD]16.69%
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
</TBODY>[/TABLE]


QUESTION: Is it because of the MAX() or MIN() in column B?
What should be modified in the formula to return the correct date?

1. Aithough correct but insufficient, we often get

B9, just enter and copy down:

=INDEX($B$1:$K$1,MATCH($B9,INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0),0))

2. Better because correct and complete...

B9, control + shift + enter and copy down:

=IFERROR(INDEX($B$1:$K$1,SMALL(IF(INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0)=$B9,
  COLUMN($B$1:$K$1)-COLUMN($B$1)+1),COLUMNS($C9:C9))),"")

 

[cyrilbrd]

1. Aithough correct but insufficient, we often get

B9, just enter and copy down:

=INDEX($B$1:$K$1,MATCH($B9,INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0),0))

2. Better because correct and complete...

B9, control + shift + enter and copy down:

=IFERROR(INDEX($B$1:$K$1,SMALL(IF(INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0)=$B9,
  COLUMN($B$1:$K$1)-COLUMN($B$1)+1),COLUMNS($C9:C9))),"")
[/SIZE]

Thanks for the feedback, prior to your reply I was using CSE =IFERROR(INDEX($B$1:$K$1,SMALL(IF(INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0)=$B9,
COLUMN($B$1:$K$1)-COLUMN($B$1)+1),COLUMNS($C9:C9))),""), glad to see that my choice does actually make sense although this formula would allow multiple values to be returned if both cdt were met...
Much appreciated as always.

QUESTION:

In the original dataset that I have the blanks are replaced by #N/A in order to make sure that they are not plotted on the dashboards.
The max and min are computed with a formula such as CSE =MAX(IF(daterange>=date1,IF(daterange<=date2,(IF(NOT(ISNA(AB7:CI7)),AB7:CI7))))) where AB7 to CI7 holds either values or #N/A.

Now the formula that I used and that you suggested does not work with this setup. It works if the #N/A are replaced with blanks. Not that it affect me much as I am using this dataset more for fetching info rather than plotting.
Any advice on why the presence of #N/A throws of the formula?

 

[cyrilbrd]

This is what I did :

=IFERROR(INDEX($B$1:$K$1,SMALL(IF(INDEX(IF(NOT(ISNA($B$2:$K$7)),$B$2:$K$7),MATCH($A9,$A$2:$A$7,0),0)=$B9, COLUMN($B$1:$K$1)-COLUMN($B$1)+1),COLUMNS($C9:C9))),"")
Would that be alright? It works so far but is it efficient?</pre>