Formula to return column header

btadams

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,943
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,
 
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
cyrilbrd said:
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?
Click to expand...

Nicola has just 1 S. Her second recod is an N. If you change that N to S, you'll get week 3 also.
 
Upvote 0
Aladin Akyurek said:
Nicola has just 1 S. Her second recod is an N. If you change that N to S, you'll get week 3 also.
Click to expand...

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>
cdt1
cdt2
Week 1
Week 2Week 3
Week 4
Nicola
S
T
VT
D

<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>
</tbody>

cdt1 Nicola
cdt2 S
cdt3 T (two T within cdt1 and cdt2)
result expected is week1 and week3
does this clarifies my query?
 
Upvote 0
cyrilbrd said:
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>
cdt1
cdt2
Week 1
Week 2
Week 3
Week 4
Nicola
S
T
V
T
D

<TBODY>
</TBODY>

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

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

Rank
Week 1
Week 2
Week 3
Week 4
cdt1
Nicola
Nicola
S
T
V
T
D
cdt2
S
Kevin
P
T
D
V
D
cdt3
T
Nicola
N
V
D
T
V
count
2
Sam
S
T
V
V
T
result
Week 1
Week 3
Peter
P
D
D
D
D
Peter
D
V
V
V
D
Roger
D
D
V
D
V
Kevin
A
D
T
D
D
Michael
Q
V
V
T
V
Peter
W
V
V
V
V

<TBODY>
</TBODY>

I4, control+shift+enter, not just enter:
Rich (BB code): 
=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:
Rich (BB code): 
=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)))))),"")
 
Last edited:
Upvote 0
Aladin Akyurek said:
Rich (BB code): 
=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))

Rich (BB code): 
=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)))))),"")
Click to expand...

Thanks Aladin, sorry for late reply.
It works perfectly.
 
Upvote 0
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-10Feb-10Mar-10Apr-10May-10Jun-10Jul-10Aug-10Sep-10Oct-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?
 
Upvote 0
cyrilbrd said:
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?
Click to expand...


1. Aithough correct but insufficient, we often get

B9, just enter and copy down:
Rich (BB code): 
=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:
Rich (BB code): 
=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))),"")
Rich (BB code):
 
Upvote 0
Aladin Akyurek said:
1. Aithough correct but insufficient, we often get

B9, just enter and copy down:
Rich (BB code): 
=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:
Rich (BB code): 
=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]
Click to expand...

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?


 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Lookup value in table, return the team header above
2
Replies
10
Views
150
Domenic
D
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
366
Nathan Asius
N
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
566
MissDB101
M
Z
  • Question
Count All values in every 3rd column
Replies
4
Views
675
Peter_SSs
Peter_SSs
J
  • Question
Trying to return text from a column on different sheet and getting #value error.
2
Replies
10
Views
239
Alex Blakenburg
A

Forum statistics

Threads
1,215,410
Messages
6,124,755
Members
449,187
Latest member
hermansoa

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top