Creating searchable text formula in helper column looking to index match results

[RicardoCubed]

I have a sheet which is populated using index match formulas pulling data from other sheet locations.

Is there a formula that I can place in a "helper column" that turnsthe index match formula results to text so I and then do a search?

Essentially a formula that works like a "cut and paste as values" so I can search. I don't want to have to cut and paste each time manually.

Specifically I want to search by name. The below is an example. Want a formula for D2 to copy down.

Thanks for help.

	A	B	C	D
1	Name	Depart	B	Helper
2	Harry Thomas	A	John Jones
3	John Jones	B	Harvey Hickock
4	George Smith	C	Phil Ant
5	Harvey Hickock	B	-
6	Mary Cook	C	-
7	Phil Ant	B	-
8	Barry Rich	A

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A2))),"-")} C3 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A3))),"-")} C4 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A4))),"-")} C5 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A5))),"-")} C6 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A6))),"-")} C7 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$C$1,ROW($A$2:$A$8)-ROW(A$2)+1),ROWS(A$2:A7))),"-")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>