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,

 

[Aladin Akyurek]

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?

I had this...

=IFERROR(INDEX($B$1:$K$1,SMALL(IF(ISNUMBER(INDEX($B$2:$K$7,MATCH($A9,$A$2:$A$7,0),0)),
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))),"")

in mind. Invokes one function less than what you have.

 

[cyrilbrd]

Thanks for the formula. Will look into that thoroughly.

Merry Christmas!

 

[cyrilbrd]

Given the same layout in A1:

	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>

What would be the best approach to return the first column knowing the value and the date?
Example:

'6.82%' on 'Jun-10' would return 'THOSE'

 

[Aladin Akyurek]

Given the same layout in A1:

	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>

What would be the best approach to return the first column knowing the value and the date?
Example:

'6.82%' on 'Jun-10' would return 'THOSE'

Let M1 house 6.82% and M2 Jun-10.

M3, control + shift + enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(INDEX(B2:K7,0,MATCH($M$2,$B$1:$K$1,0))=$M$1,
  ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($M$3:M3))),"")

In case the calculated reference contains any error value, again control + shift + enter and copy down...

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISNUMBER(INDEX(B2:K7,0,MATCH($M$2,$B$1:$K$1,0))),
  IF(INDEX(B2:K7,0,MATCH($M$2,$B$1:$K$1,0))=$M$1,ROW($A$2:$A$7)-ROW($A$2)+1)),
  ROWS($M$3:M3))),"")

 

[bradhog]

i like it. thanks for the information

 

[Aladin Akyurek]

i like it. thanks for the information

You are welcome.

 

[egonyo]

You'll get the header that corresponds to the first/leftmost instance of the computed MIN.
This message was edited by Aladin Akyurek on 2003-02-06 16:58

Hi Aladin, I am curious instead of returning the far left column is there a way to get this formula to return a word like "equal"? Here is my current formula and it returns equal when the buy/sell column is blank. Thank you

=IF(F206="buy",INDEX($J$1:$L$1,MATCH(MIN(J206:L206),J206:L206,0)),IF(F206="sell",INDEX($J$1:$L$1,MATCH(MAX(J206:L206),J206:L206,0)),""))

 

[Aladin Akyurek]

Hi Aladin, I am curious instead of returning the far left column is there a way to get this formula to return a word like "equal"? Here is my current formula and it returns equal when the buy/sell column is blank. Thank you

=IF(F206="buy",INDEX($J$1:$L$1,MATCH(MIN(J206:L206),J206:L206,0)),IF(F206="sell",INDEX($J$1:$L$1,MATCH(MAX(J206:L206),J206:L206,0)),""))

Not sure I understand... It's possible that MIN(J206:L206) = MAX(J206:L206) when J206 = 8, K206 = 8, and L206 = 8, for example. But, you probably mean something different. Care to clarify?

 

[egonyo]

Not sure I understand... It's possible that MIN(J206:L206) = MAX(J206:L206) when J206 = 8, K206 = 8, and L206 = 8, for example. But, you probably mean something different. Care to clarify?

Certainly, say if I am comparing 2-3 'buy' prices and a couple have the same minimum price. Instead of putting the title of the far left column (J in formula I mentioned above), is there a way for it to tell me there are duplicate minimum buy prices. This is so I can differentiate from where column J is actually the lowest buy price vs when it is defaulted to the left column on a duplicate minimum.

 

[Aladin Akyurek]

Hi Aladin, I am curious instead of returning the far left column is there a way to get this formula to return a word like "equal"? Here is my current formula and it returns equal when the buy/sell column is blank. Thank you

=IF(F206="buy",INDEX($J$1:$L$1,MATCH(MIN(J206:L206),J206:L206,0)),IF(F206="sell",INDEX($J$1:$L$1,MATCH(MAX(J206:L206),J206:L206,0)),""))

Not sure I understand... It's possible that MIN(J206:L206) = MAX(J206:L206) when J206 = 8, K206 = 8, and L206 = 8, for example. But, you probably mean something different. Care to clarify?

Certainly, say if I am comparing 2-3 'buy' prices and a couple have the same minimum price. Instead of putting the title of the far left column (J in formula I mentioned above), is there a way for it to tell me there are duplicate minimum buy prices. This is so I can differentiate from where column J is actually the lowest buy price vs when it is defaulted to the left column on a duplicate minimum.

Row\Col	F​	G​	H​	I​	J​	K​	L​
1​					jdm	beco	tya
206​	buy				0.8​	2.1​	0.8​
207​	0.8​
208​	2​
209​	jdm
210​	tya
211​

In F207 enter:

=IF($F206="buy",MIN($J$206:$L$206),MAX($J$206:$L$206))<strike></strike>

In F208 enter:

=COUNTIFS(J206:L206,F207)<strike></strike>

In F209 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$209:F209)<=$F$208,INDEX($J$1:$L$1,
  SMALL(IF($J$206:$L$206=$F$207,COLUMN($J$1:$L$1)-COLUMN($J$1)+1),
  ROWS($F$209:F209))),"")<strike></strike>