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