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
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...

[TABLE="width: 658"]
<tbody>[TR]
[TD="class: xl67, width: 41, bgcolor: transparent"]Prod[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]1/19/2012[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]1/22/2012[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]1/29/2012[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent, align: right"]2/5/2012[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent, align: right"]2/6/2012[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent, align: right"]2/9/2012[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]2/10/2012[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]2/14/2012[/TD]
[TD="class: xl65, width: 51, bgcolor: transparent"]COUNT[/TD]
[TD="class: xl69, width: 207, bgcolor: transparent, colspan: 3"]ZERO-VALUE DATES[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]fad[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1/19/2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/5/2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/10/2012[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]kad[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]lad[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/6/2012[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]nad[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/9/2012[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/10/2012[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 86"]
<TBODY>[TR]
[TD="class: xl63, width: 114, bgcolor: transparent"]fad
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Zero Value Dates
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]19-Jan-12
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]5-Feb-12
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]10-Feb-12
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

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> [TABLE="width: 325"]
<tbody>[TR]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl64, width: 65"]Week 1[/TD]
[TD="class: xl64, width: 65"]Week 2[/TD]
[TD="class: xl64, width: 65"]Week 3[/TD]
[TD="class: xl64, width: 65"]Week 4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Nicola[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Kevin[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Nicola[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Sam[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Roger[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Kevin[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Michael[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt1[/TD]
[TD="class: xl68"]Nicola[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt2[/TD]
[TD="class: xl68"]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt3[/TD]
[TD="class: xl68"]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]result[/TD]
[TD="class: xl69, width: 65"]Week 1[/TD]
[TD="class: xl69, width: 65"]Week 3[/TD]
[TD="class: xl69, width: 65"]Week 3[/TD]
[TD="class: xl69, width: 65"]Week 3[/TD]
[/TR]
</tbody>[/TABLE]

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> [TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl63, width: 65"][/TD]
[TD="class: xl64, width: 65"]Week 1[/TD]
[TD="class: xl64, width: 65"]Week 2[/TD]
[TD="class: xl64, width: 65"]Week 3[/TD]
[TD="class: xl64, width: 65"]Week 4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Nicola[/TD]
[TD="class: xl70, width: 65"]S[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Kevin[/TD]
[TD="class: xl70, width: 65"]P[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Nicola[/TD]
[TD="class: xl70, width: 65"]N[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Sam[/TD]
[TD="class: xl70, width: 65"]S[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl70, width: 65"]P[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl70, width: 65"]D[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Roger[/TD]
[TD="class: xl70, width: 65"]D[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Kevin[/TD]
[TD="class: xl70, width: 65"]A[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Michael[/TD]
[TD="class: xl70, width: 65"]Q[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]T[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 65"]Peter[/TD]
[TD="class: xl70, width: 65"]W[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[TD="class: xl66"]V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt1[/TD]
[TD="class: xl69, width: 65"]Nicola[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt2[/TD]
[TD="class: xl69, width: 65"]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]cdt3[/TD]
[TD="class: xl69, width: 65"]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 65"]result[/TD]
[TD="class: xl68, width: 65"]Week 1[/TD]
[TD="class: xl68, width: 65"]Week 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...


[TABLE="width: 575"]
<TBODY>[TR]
[TD="class: xl65, width: 94, bgcolor: white"][/TD]
[TD="class: xl65, width: 85, bgcolor: white"]Week 1
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]Week 2
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]Week 3
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]Week 4
[/TD]
[TD="class: xl66, width: 29, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 22, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 70, bgcolor: white"]cdt1
[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]Nicola
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Nicola
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]D
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 70, bgcolor: white"]cdt2
[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]Michael
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Kevin
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]D
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 70, bgcolor: white"]cdt3
[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Nicola
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]V
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 70, bgcolor: white"]result
[/TD]
[TD="class: xl65, width: 81, bgcolor: white"]Week 1
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]Week 3
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Sam
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]T
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Peter
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]D
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Peter
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]D
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Roger
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]V
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Kevin
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]D
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]D
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Michael
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]T
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]V
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 94, bgcolor: white"]Peter
[/TD]
[TD="class: xl65, width: 85, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 86, bgcolor: white"]V
[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]V
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

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...

[TABLE="width: 564"]
<TBODY>[TR]
[TD="class: xl66, width: 97, bgcolor: white"][/TD]
[TD="class: xl66, width: 64, bgcolor: white"]Rank
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]Week 1
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]Week 2
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]Week 3
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]Week 4
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]cdt1
[/TD]
[TD="class: xl66, width: 74, bgcolor: white"]Nicola
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Nicola
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]S
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]cdt2
[/TD]
[TD="class: xl66, width: 74, bgcolor: white"]S
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Kevin
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]P
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]cdt3
[/TD]
[TD="class: xl66, width: 74, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Nicola
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]N
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: white"]result
[/TD]
[TD="class: xl65, bgcolor: transparent"]Week 1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Sam
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]S
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]P
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Roger
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Kevin
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]A
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]D
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Michael
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]Q
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]T
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 97, bgcolor: white"]Peter
[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]W
[/TD]
[TD="class: xl66, width: 80, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 84, bgcolor: white"]V
[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]V
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

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

D
  • Question Question
Help with "list source must be delimited list, or a reference" error
Replies
1
Views
94
myall_blues
myall_blues
S
  • Question Question
Split lines in cell into rows while retaining font color in the new rows
Replies
4
Views
236
Subash_G
S
W
  • Question Question
Counting the Number of times text appears by criteria
Replies
3
Views
97
Peter_SSs
Peter_SSs
S
  • Question Question
ChooseCols with VStack and then a filter issue
Replies
1
Views
779
Fluff
Fluff
T
  • Solved
Verify Whether a Day # Falls Into A Date Range
Replies
2
Views
75
The_Vanguard
T

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