Array Formula to return Header
 

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

Array Formula to return Header
 

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

Array Formula to return Header
 

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

Array Formula to return Header
 

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

Array Formula to return Header
 

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

Array Formula to return Header
 

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

Array Formula to return Header
 

On Thursday, September 29, 2016 at 1:33:02 AM UTC-7, Claus Busch wrote:
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.


Hi Claus,

The formula works fine but the explanation given about the data was misleading.

Here is a worksheet with some expected examples shown in the Bronze, Silver and Gold columns A, B, C.

https://www.dropbox.com/s/tknhdsiq1e...Gold.xlsm?dl=0

Mo gets bronze date of April with 500 accumulated by that month.
Syed gets bronze, silver, gold all on same month of September.

So it is the month they hit 500 returns the header to bronze column A.
The month they hit 1500 returns the header to silver column B.
And the month they hit 5000 returns the header to gold column C.

I am wondering if this will require VB code instead of formula. I am working on code as I don't know where to start with a formula.

Howard

Array Formula to return Header
 

Hi Howard,

Am Thu, 29 Sep 2016 11:27:33 -0700 (PDT) schrieb L. Howard:

Here is a worksheet with some expected examples shown in the Bronze, Silver and Gold columns A, B, C.

https://www.dropbox.com/s/tknhdsiq1e...Gold.xlsm?dl=0

Mo gets bronze date of April with 500 accumulated by that month.
Syed gets bronze, silver, gold all on same month of September.

So it is the month they hit 500 returns the header to bronze column A.
The month they hit 1500 returns the header to silver column B.
And the month they hit 5000 returns the header to gold column C.

download "Bronze Silver Gold" from:
https://onedrive.live.com/redir?resi...=folder%2cxlsm


Regards
Claus B.
--
Windows10
Office 2016

Array Formula to return Header
 

On Thursday, September 29, 2016 at 12:01:48 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Thu, 29 Sep 2016 11:27:33 -0700 (PDT) schrieb L. Howard:

Here is a worksheet with some expected examples shown in the Bronze, Silver and Gold columns A, B, C.

https://www.dropbox.com/s/tknhdsiq1e...Gold.xlsm?dl=0

Mo gets bronze date of April with 500 accumulated by that month.
Syed gets bronze, silver, gold all on same month of September.

So it is the month they hit 500 returns the header to bronze column A.
The month they hit 1500 returns the header to silver column B.
And the month they hit 5000 returns the header to gold column C.

download "Bronze Silver Gold" from:
https://onedrive.live.com/redir?resi...=folder%2cxlsm


Regards
Claus B.
--

Wow, very nice indeed!!

Never cease to amaze.

Thanks, Claus.

Howard