Sorting data populated with an HLOOKUP

I have a rather large set of data that has certain values populated by HLOOKUPS. I need to be able to sort the overall data set and have the HLOOKUP rows remain intact. I will explain this below with an example of what I am trying to do.

I am working on a physician database which is currently sorted by specialty. For each physician row, there is an HLOOKUP that is benchmarking a value on the (current) Summary sheet to another worksheet (Benchmark Summary). Here is the formula: =HLOOKUP(S8/O8,'Benchmark Summary'!$C8:$CG$251,'Benchmark Summary'!$A8,1)

The formula works great as long as the Summary worksheet is not sorted in any other manner. The Benchmark Summary sheet, where the HLOOKUP is matching the specialty name, is populated by the Summary sheet. So, in the above formula, I am benchmarking the specialty of Emergency Medicine. Column B in Benchmark Summary is populated with Emergency Medicine in the same row (row 8). This then works on many other sheets to pull in data specific to that specialty in the Benchmark Summary C8:CG251. If I were to then change the specialty to Family Medicine on the Summary sheet, then Benchmark Summary B8 would also change to Family Medicine, and pull in the appropriate benchmarks. (Bear with me, the problem comes in a few moments.)

The problem becomes that the row indicators within the formula do not change if I resort the data, but the specialties will change. So if I resort, and the above example goes from Row 8 to Row 22 to function retains the original anchor to Row 8. The issue is that the specialty in Row 8 is no longer Emergency Medicine, and the formula still points to row 8 instead of updating to row 22. Again, the formula follows: =HLOOKUP(S22/O22,'Benchmark Summary'!$C8:$CG$251,'Benchmark Summary'!$A8,1)

Any thoughts on how to unanchor the formula (NOTE: there is not an absolute reference ($) attached to the rows of the red numbers, just the columns)? I think Excel is trying to help me by retaining the original designation, but in fact I need it to do the opposite and update the above red numbers to row 22.

There is a lot of information in it that I cannot legally disclose. I'll work on a smaller, blinded set of data, but in the meantime any suggestions or questions you may have would be much appreciated!

Alright all, I have attached a section of the workbook I am working on with blinded data. Again, the problem is within the HLOOKUP, so when it is sorted by MGMA Specialty, that is the original order with no issues. When you sort by Comp Data, then you'll notice that the rows the HLOOKUP are referencing are out of sync.

Test.xlsx