I have a very large table with repeating values based off of a ID column. I'm after a function/combination of excel functions that would work to return the ID number where all instances of a searched value are met?
I dont have access to the "Filter" function so im looking for an alternative to this.
E.g, I wish to find all instances of F in the table. From here the function should return the row/associated "ID" This would then output 2,4,25 where the matches in the table occur
| ID | B | C | D | ... | BB | BC |
|---|---|---|---|---|---|---|
| SEARCH | F | |||||
| 1 | A | B | C | D | E | G |
| 2 | D | E | G | F | G | S |
| 3 | T | V | A | K | H | E |
| 4 | Y | F | J | N | R | K |
| 5 | I | O | W | H | X | Z |
| ... | ||||||
| 25 | T | K | Q | E | H | F |
CodePudding user response：
array formula：
=IFERROR(INDEX(A:A,SMALL(IF(B$1:G$500="F",ROW($1:$500)),ROW(A1))),"")
CodePudding user response：
You can try creating an extra column at the end and use this formula in each cell down that column. Make sure your "Search Cell" remains same in the formula though: Drag this down from first cell down to all rows but make sure the search cell remains same. i.e. B1 in my example. I have added a last extra column H in my example to do this calculaton and the result:
=IF(IFERROR(MATCH(B1,B2:G2,0),"")<>"",A2,"")
