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
Helper
Harry Thomas	John Jones
John Jones	Harvey Hickock
George Smith	Phil Ant
Harvey Hickock	-
Mary Cook	-
Phil Ant	-
Barry Rich

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]B[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]A[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]B[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]C[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]B[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]C[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]B[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]{=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))),"-")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]