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
ekaminski said:
Hello All- This information has been very useful, I have one small problem. The data I am looking for is contained on a seperate tab named "formulas".

The formula

=INDEX(H1:M1,MATCH(MAX(B2:E2),B2:E2,0))

Works perfectly when all the data is contained on the same page, but I would like to move the data to another tab, so my cover page only contains necessary info.

Any advice you can provide is greatly appreciated!

EK
Click to expand...

Put formulas! (note the exclamation mark) in front of the cell reference that are in the formula sheet... that is how you reference a sheet other than the active sheet and if your sheet name contains spaces (plus a few others), enclose the sheet name (not the exclamation mark) in single quotes (apostrophes). So, assuming B2:E2 is on the formula sheet and H1:M1 is on the sheet where the formula is placed, you would use this...

=INDEX(H1:M1,MATCH(MAX(formulas!B2:E2),formulas!B2:E2,0))
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
ekaminski said:
Hello All- This information has been very useful, I have one small problem. The data I am looking for is contained on a seperate tab named "formulas".

The formula

=INDEX(H1:M1,MATCH(MAX(B2:E2),B2:E2,0))

Works perfectly when all the data is contained on the same page, but I would like to move the data to another tab, so my cover page only contains necessary info.

Any advice you can provide is greatly appreciated!

EK
Click to expand...

Are both ranges H1:M1 and B2:E2 on 'formulas'?

Note that the formula you quote returns just one result even there are two or more max instances.
 
Upvote 0
RhysD said:
To do this just place $ in the header reference cells. Such as =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))

Thanks for this formula. Very helpful and simpler than my attempt. I knew Index would be useful here but couldn't visualize the formula. I've used a MAX rather than MIN but logic is identical.
Click to expand...

This forumula does exactly what I need it to do. My question is how can I convert this into a UDF? Any help is appreciated.
 
Upvote 0
RhysD said:
To do this just place $ in the header reference cells. Such as =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))

Thanks for this formula. Very helpful and simpler than my attempt. I knew Index would be useful here but couldn't visualize the formula. I've used a MAX rather than MIN but logic is identical.
Click to expand...

nnklem said:
This forumula does exactly what I need it to do. My question is how can I convert this into a UDF? Any help is appreciated.
Click to expand...

B1:E1 is:

NAD,RAD,ZAD,VAD

B2:E2 is:

24,40,40,24

Given the foregoing, the formula will not yield what one would expect.
 
Upvote 0
Aladin Akyurek said:
Consider...

Prod1/19/20121/22/20121/29/20122/5/20122/6/20122/9/20122/10/20122/14/2012COUNTZERO-VALUE DATES
fad02501290731/19/20122/5/20122/10/2012
kad52211111290
lad5101210010101012/6/2012
nad610410800622/9/20122/10/2012

<tbody>
</tbody>


Y2, just enter and copy down:
Rich (BB code): 
=COUNTIF(Q2:X2,0)

Z2, control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=IF(COLUMNS($Z2:Z2)<=$Y2,INDEX($Q$1:$X$1,SMALL(IF($Q2:$X2=0,
 COLUMN($Q2:$X2)-COLUMN($Q2)+1),COLUMNS($Z2:Z2))),"")
Click to expand...

Aladin, what if two conditions such as:
cdt1 : fad
cdt2: zero (0) as stated in example.
Result expected would be in 3 different cells, 1/19/12, 2/5/12 and 2/10/12
should we add a match to link cdt1 to the range P2:P5?
 
Upvote 0
cyrilbrd said:
Aladin, what if two conditions such as:
cdt1 : fad
cdt2: zero (0) as stated in example.
Result expected would be in 3 different cells, 1/19/12, 2/5/12 and 2/10/12
should we add a match to link cdt1 to the range P2:P5?
Click to expand...

Let Sheet1, P1:X5, house the data, with dates in Q1:X1 in the header row, and Sheet2 the processing...

Sheet2

fad
0
Zero Value Dates
19-Jan-12
5-Feb-12
10-Feb-12

<TBODY>
</TBODY>

A1: fad
A2: 0

A4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$Q$1:$X$1,
  SMALL(IF(INDEX(Sheet1!$Q$2:$X$5,MATCH(K$1,Sheet1!$P$2:$P$5,0),0)=0,
  COLUMN(Sheet1!$Q$1:$X$1)-COLUMN(Sheet1!$Q$1)+1),ROWS($A$4:A4))),"")

If you would want to exclude empty cells being read as zero-valued...
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$Q$1:$X$1,
  SMALL(IF(ISNUMBER(INDEX(Sheet1!$Q$2:$X$5,MATCH(K$1,Sheet1!$P$2:$P$5,0),0)),
  IF(INDEX(Sheet1!$Q$2:$X$5,MATCH(K$1,Sheet1!$P$2:$P$5,0),0)=0,
  COLUMN(Sheet1!$Q$1:$X$1)-COLUMN(Sheet1!$Q$1)+1)),ROWS($A$4:A4))),"")
 
Upvote 0
Aladin Akyurek said:
A4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$Q$1:$X$1,
  SMALL(IF(INDEX(Sheet1!$Q$2:$X$5,MATCH(A$1,Sheet1!$P$2:$P$5,0),0)=0,
  COLUMN(Sheet1!$Q$1:$X$1)-COLUMN(Sheet1!$Q$1)+1),ROWS($A$4:A4))),"")


[/code]
Click to expand...

A$1 instead of K1 right?
What if we want the data to be presented in a row, would
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$Q$1:$X$1,
  SMALL(IF(INDEX(Sheet1!$Q$2:$X$5,MATCH($A$1,Sheet1!$P$2:$P$5,0),0)=0,
  COLUMN(Sheet1!$Q$1:$X$1)-COLUMN(Sheet1!$Q$1)+1),COLUMNS($A$4:A4))),"")
 be sufficient?
 
Upvote 0
cyrilbrd said:
A$1 instead of K1 right?
Click to expand...
Yes. (I need another cup of coffee I guess.)

What if we want the data to be presented in a row, would
Rich (BB code): 
=IFERROR(INDEX(Sheet1!$Q$1:$X$1,
  SMALL(IF(INDEX(Sheet1!$Q$2:$X$5,MATCH($A$1,Sheet1!$P$2:$P$5,0),0)=0,
  COLUMN(Sheet1!$Q$1:$X$1)-COLUMN(Sheet1!$Q$1)+1),COLUMNS($A$4:A4))),"")
be sufficient?
Click to expand...

Yes.
 
Upvote 0
Thanks for the feedback, considering another cup of coffee, what would you recommend for three (3) conditions, such as:
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; }.xl63 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; white-space: normal; }.xl64 { 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; }.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(253, 233, 217); white-space: normal; }.xl66 { text-align: center; border: 0.5pt solid windowtext; }.xl67 { font-size: 11pt; font-family: "Calibri (Body)"; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 233, 217); white-space: normal; }.xl68 { border: 0.5pt solid windowtext; }.xl69 { font-size: 11pt; font-family: "Calibri (Body)"; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(218, 238, 243); white-space: normal; }</style>
Week 1Week 2Week 3Week 4
NicolaTVTD
KevinTDVD
NicolaVDTV
SamTVVT
PeterDDDD
PeterVVVD
RogerDVDV
KevinDTDD
MichaelVVTV
PeterVVVV
cdt1Nicola
cdt2Michael
cdt3T
resultWeek 1Week 3Week 3Week 3

<tbody>
</tbody>

Would that be complicated because two conditions are within the same range?

Would that be easier?
<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; }.xl63 { font-size: 11pt; font-family: "Calibri (Body)"; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; white-space: normal; }.xl64 { 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; }.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(253, 233, 217); white-space: normal; }.xl66 { text-align: center; border: 0.5pt solid windowtext; }.xl67 { font-size: 11pt; font-family: "Calibri (Body)"; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 233, 217); white-space: normal; }.xl68 { font-size: 11pt; font-family: "Calibri (Body)"; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(218, 238, 243); white-space: normal; }.xl69 { font-size: 11pt; font-family: "Calibri (Body)"; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(228, 223, 236); white-space: normal; }.xl70 { 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; }</style>
Week 1Week 2Week 3Week 4
NicolaSTVTD
KevinPTDVD
NicolaNVDTV
SamSTVVT
PeterPDDDD
PeterDVVVD
RogerDDVDV
KevinADTDD
MichaelQVVTV
PeterWVVVV
cdt1Nicola
cdt2S
cdt3T
resultWeek 1Week 3

<tbody>
</tbody>

Would the formula be different?
 
Last edited:
Upvote 0
cyrilbrd said:
Thanks for the feedback, considering another cup of coffee, what would you recommend for three (3) conditions, such as:

[[ Case 1 ]]

Would that be complicated because two conditions are within the same range?

Would that be easier?

[[ Case 2 ]]

Would the formula be different?
Click to expand...

First case...


Week 1
Week 2
Week 3
Week 4
cdt1
Nicola
Nicola
T
V
T
D
cdt2
Michael
Kevin
T
D
V
D
cdt3
T
Nicola
V
D
T
V
result
Week 1
Week 3
Sam
T
V
V
T
Peter
D
D
D
D
Peter
V
V
V
D
Roger
D
V
D
V
Kevin
D
T
D
D
Michael
V
V
T
V
Peter
V
V
V
V

<TBODY>
</TBODY>

I4, control+shift+enter, not just enter, and copy across:
Rich (BB code): 
=IFERROR(INDEX($B$1:$E$1,SMALL(IF($B$2:$E$11=$I$3,
  IF(ISNUMBER(MATCH($A$2:$A$11,$I$1:$I$2,0)),
  IF(ISNA(MATCH($B$1:$E$1,$H$4:H4,0)),COLUMN($B$1:$E$1)-COLUMN($B$1)+1))),
  COLUMNS($I$4:I4))),"")

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
result
Week 1
Sam
S
T
V
V
T
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, and copy across:
Rich (BB code): 
=IFERROR(INDEX($C$1:$F$1,SMALL(IF($B$2:$B$11=$I$2,
  IF($C$2:$F$11=$I$3,IF($A$2:$A$11=$I$1,IF(ISNA(MATCH($C$1:$F$1,$H$4:H4,0)),
  COLUMN($C$1:$F$1)-COLUMN($C$1)+1)))),COLUMNS($I$4:I4))),"")

As can be seen from the formulas, the processing is to a large extent similar.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Returning one single numer.
2
Replies
12
Views
196
Eric W
Eric W
D
  • Solved
Lookup value in table, return the team header above
2
Replies
10
Views
203
Domenic
D
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
395
Nathan Asius
N
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
668
MissDB101
M
Z
  • Question
Count All values in every 3rd column
Replies
4
Views
715
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,075
Messages
6,128,662
Members
449,462
Latest member
Chislobog

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