Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Column Header from an Index/array, finding Large with a userinput date alovell Excel Worksheet Functions 0 November 16th 12 09:32 AM
Return column header within an array when a value is met ECDNewEngland Excel Worksheet Functions 9 October 20th 08 10:08 PM
Lookup Value in Range/Array and Return Column Header Value [email protected] Excel Discussion (Misc queries) 3 June 16th 06 07:05 PM
Lookup Value in Range/Array and Return Column Header Value [email protected] Excel Worksheet Functions 3 June 16th 06 07:05 PM
Lookup Value in Range/Array and Return Column Header Value [email protected] Excel Programming 3 June 16th 06 07:05 PM


All times are GMT +1. The time now is 11:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"