how to prevent cell referances from being deleted

[Dark0Prince]

I have a table of data and when I delete the 5th row the data is replaces from row 6 because it moved up. How can I make sure my referances stay the same instead of showing #REF !
Below is an example of two formulas I've tried.

=PROPER(IFERROR(INDEX(PIF_REPORT!$H$5,MATCH(PIF_REPORT!$H$4,PIF_REPORT!$H$4,0)),""))&" " & PROPER(PIF_REPORT!$I$5)

This one had an issue of adding up every cell it can in that column instead of just the first row. (eg. all first & last names in one cell)

=PROPER(CONCAT(Table_Query_from_DEF[First_Name]," ",Table_Query_from_DEF[Last_Name]))

 

[Dark0Prince]

I found something that was working for a little while until I can to my search formula. OFFSET worked up until I hit this formula and I don't know how to add it to this one.

=IF(ISNUMBER(SEARCH("15250E",PIF_REPORT!B5)),"NOT FOUND")

I'd like OFFSET to start at B4 and only go down 1 row to look for the 15250E.
I Tried this but I get an error:

=IF(OFFSET(SEARCH("15250E",)PIF_REPORT!$B$4,1,0,1,1),"Advantage Billing Manager")

The reason I can't use the search formula but itself is because in my table the data keeps moving around and my headers don't.