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

[Johnrd1963]

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

 

[Claus Busch]

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

 

[Kevin@Radstock]

Hi

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

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

 

[Johnrd1963]

Hi John,

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

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

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

 

[Claus Busch]

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

 

[Johnrd1963]

Hi John,

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

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

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

Thanks, Joh

+-------------------------------------------------------------------
|Filename: Schedule.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=701
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Johnrd1963]

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------