Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
Hi
have a look at INDEX & MATCH, see if that suits your requirements. http://www.contextures.com/xlFunctions03.html Quote:
|
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
|
|||
|
|||
Quote:
Thanks, John |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
|
|||
|
|||
Thanks Claus. Making this an an array formula made the difference. It works great now.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for 2 values and returning the sum... | Excel Discussion (Misc queries) | |||
Returning a text by searching the whole workbook | Excel Discussion (Misc queries) | |||
Searching a column and returning a yes or no answer | Excel Worksheet Functions | |||
Sorting/Searching & returning data | Excel Programming | |||
Searching and returning row number of a value | Excel Worksheet Functions |