Hi John,

Am Thu, 15 Nov 2012 19:56:23 +0000 schrieb Johnrd1963:

I have already tried a variation of this
formula with the SUMPRODUCT function. It doesn't work (returns an
error) when used with dates that are listed more than once. What I need
when it finds more than one cell with the same date, I need it to return
the row/column header of the first occurring cell, from top to bottom,
in the table.

your search date still in L1
for the row header try:
=IF(ROWS($1:1)COUNTIF($B$2:$J$100,$L$1),"",INDEX( $A$1:$A$100,SMALL(IF($B$1:$J$100=$L$1,ROW($1:$100) ),ROW(A1))))
the column header:
=INDEX($A$1:$J$1,MATCH($L$1,INDIRECT(SMALL(IF($B$1 :$J$100=$L$1,ROW($1:$100)),ROW(A1))&":"&SMALL(IF($ B$1:$J$100=$L$1,ROW($1:$100)),ROW(A1))),0))
and copy down.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I put these last two formulas in L2 and L3 and they return an error (#VALUE!) It seems the IF($B$1:$J$100=$L$1,ROW($1:$100)) part is causing the error. I have attached the file for this.

Thanks, John

Hi John,

Am Thu, 29 Nov 2012 21:44:20 +0000 schrieb Johnrd1963:

I put these last two formulas in L2 and L3 and they return an error
(#VALUE!) It seems the IF($B$1:$J$100=$L$1,ROW($1:$100)) part is
causing the error. I have attached the file for this.

sorry, I forgot to write that this array formulas have to be entered
with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus. Making this an an array formula made the difference. It works great now.