Trying to return the header of the cell in the range E2:P2 that has a value greater than 499 and less than 1500.

Formulas, as is, returns header from P column while value of 550 is in L2.


=INDEX($E$1:$P$1,MAX(E2:P2499,E2:P2<1500,COLUMN(E 2:P2)-COLUMN($E$1))+1)

Thanks
Howard

Hi Howard,

Am Wed, 28 Sep 2016 11:13:24 -0700 (PDT) schrieb L. Howard:

Trying to return the header of the cell in the range E2:P2 that has a value greater than 499 and less than 1500.

Formulas, as is, returns header from P column while value of 550 is in L2.

=INDEX($E$1:$P$1,MAX(E2:P2499,E2:P2<1500,COLUMN(E 2:P2)-COLUMN($E$1))+1)

if your Excel version is 2007 or newer:

=INDEX(E1:P1,,MATCH(MAXIFS(E2:P2,E2:P2,"499",E2:P 2,"<1500"),E2:P2,0))


Regards
Claus B.
--
Windows10
Office 2016

On Wednesday, September 28, 2016 at 11:56:40 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Wed, 28 Sep 2016 11:13:24 -0700 (PDT) schrieb L. Howard:

Trying to return the header of the cell in the range E2:P2 that has a value greater than 499 and less than 1500.

Formulas, as is, returns header from P column while value of 550 is in L2.

=INDEX($E$1:$P$1,MAX(E2:P2499,E2:P2<1500,COLUMN(E 2:P2)-COLUMN($E$1))+1)

if your Excel version is 2007 or newer:

=INDEX(E1:P1,,MATCH(MAXIFS(E2:P2,E2:P2,"499",E2:P 2,"<1500"),E2:P2,0))


Regards
Claus B.
--


Hi Claus,

The formula returns the #NAME? error on the sheet, and the function wizard points to the match function, result = #NAME? (red)

Using Excel 2010 and Array Enter for the formula. The value in L2 is 550.

When I follow the formula through in the formula box, it sure acts like it should work as I parse through the arguments.

Howard

Hi Howard,

Am Wed, 28 Sep 2016 13:50:09 -0700 (PDT) schrieb L. Howard:

The formula returns the #NAME? error on the sheet, and the function wizard points to the match function, result = #NAME? (red)

Using Excel 2010 and Array Enter for the formula. The value in L2 is 550.

When I follow the formula through in the formula box, it sure acts like it should work as I parse through the arguments.

check the formats of the numbers in E2:P2.
Or try:

=INDEX(E1:P1,MATCH(MAX(IF(E2:P2<1500,E2:P2)),E2:P2 ,0))
*Array formula


Regards
Claus B.
--
Windows10
Office 2016

check the formats of the numbers in E2:P2.
Or try:

=INDEX(E1:P1,MATCH(MAX(IF(E2:P2<1500,E2:P2)),E2:P2 ,0))
*Array formula


Regards
Claus B.
--

Hi Claus,

Format is general, actual numbers.

The formula you just posted works with the single threshold of 500.
There are two other thresholds, 1500 and 5000.

I am thinking there does not need to be a 'range', like 499 and < 1500 etc.

So I am using =IFERROR(INDEX($E$1:$P$1,MATCH(MAX(IF($E$2:$P$2=50 0,$E$2:$P$2)),$E$2:$P$2,0)),"") pulled across B, C & D.

B for 500, C for 1500 & C for 5000. And this looks like it meet requirements.

Thanks much.

Howard

Hi Howard,

Am Thu, 29 Sep 2016 00:32:29 -0700 (PDT) schrieb L. Howard:

I am thinking there does not need to be a 'range', like 499 and < 1500 etc.

So I am using =IFERROR(INDEX($E$1:$P$1,MATCH(MAX(IF($E$2:$P$2=50 0,$E$2:$P$2)),$E$2:$P$2,0)),"") pulled across B, C & D.

B for 500, C for 1500 & C for 5000. And this looks like it meet requirements.

I don't know what you want to do.
Can you send me an example workbook with the expected output?


Regards
Claus B.
--
Windows10
Office 2016