=IFERROR(INDEX(Table1,SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")
How to adapt it to return row numbers only, and to do so concatonated in the same cell.
Likewise, or similarly,
=IFERROR(INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1), "")
Which is a brilliant formula, with ability to search in a n by m table and return the first match (unfortuntely) , would like to adapt it to return all and any match instances.
(and in the same cell the forumla is intered in if possible).
So far, I've been able to avoid trouble, since my data column range im searching for (text strings in C:C) has known and finite corresponding values in the table in searching in , and ive employed a countif by its values, so I know how many duplicates C1,C2,C3,.., Cn have.
(so I check these manually in the table)
How to adapt it to return row numbers only, and to do so concatonated in the same cell.
Likewise, or similarly,
=IFERROR(INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1), "")
Which is a brilliant formula, with ability to search in a n by m table and return the first match (unfortuntely) , would like to adapt it to return all and any match instances.
(and in the same cell the forumla is intered in if possible).
So far, I've been able to avoid trouble, since my data column range im searching for (text strings in C:C) has known and finite corresponding values in the table in searching in , and ive employed a countif by its values, so I know how many duplicates C1,C2,C3,.., Cn have.
(so I check these manually in the table)
