Need formulas for searching for a value in a table and returning row/column name
 

I manage a maintenance schedule for equipment. I have a table with 11 columns and about 80 rows. The top row lists the equipment name. The left most column lists the type of maintenance required. The intersecting cells in the table have the date which the maintenance for the particular piece of equipment is due to be done. Some of the dates in the cells occur more than once and some cells are blank. I need 2 formulas, one which return the equipment name (top row) for the first occurrence of a specific date entered in a cell on the worksheet and one which will do the same for the work required (left most column). If the formula fails to find a date, it needs to return a blank space in the cell.

Need formulas for searching for a value in a table and returning row/column name
 

Hi John,

Am Thu, 15 Nov 2012 00:14:43 +0000 schrieb Johnrd1963:

I manage a maintenance schedule for equipment. I have a table with 11
columns and about 80 rows. The top row lists the equipment name. The
left most column lists the type of maintenance required. The
intersecting cells in the table have the date which the maintenance for
the particular piece of equipment is due to be done. Some of the dates
in the cells occur more than once and some cells are blank. I need 2
formulas, one which return the equipment name (top row) for the first
occurrence of a specific date entered in a cell on the worksheet and one
which will do the same for the work required (left most column). If the
formula fails to find a date, it needs to return a blank space in the
cell.

your search date in L1
for equipment name:
=INDEX(A1:A100,SUMPRODUCT((A1:J100=L1)*(ROW(1:100) )))
for work:
=INDEX(A1:J1,SUMPRODUCT((A1:J100=L1)*(COLUMN(A:J)) ))


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

Hi

have a look at INDEX & MATCH, see if that suits your requirements.

http://www.contextures.com/xlFunctions03.html

Thanks for the response. 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.

Thanks for the response. 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.

Need formulas for searching for a value in a table and returning row/column name
 

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

Need formulas for searching for a value in a table and returning row/column name
 

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.