How to Find Corresponding Cells in Row Based on Lookup in Google Sheets

ss.getActiveRange() selects the currently selected or edited range. From reading your script I see you meant the entire range of data, which would be ss.getDataRange(). Other than that, the logic is okay.

Other suggestions:

• Don't use methods when unnecessary, you get time and tablet that are never used.
• Consider getting all relevant values in one getValues call, and then loop over the resulting JavaScript array, referring to individual cells with range.getCell(..).clear only when actually needed.
• Consider running the script by a trigger on form submission, making use of the event object. This simplifies things a lot: see below.

function statCheck(e) {
  if (e.values[4] === 'Recovering) {  
    e.range.offset(0, 1, 1, 2).clear();
    e.range.offset(0, 4, 1, 1).setValue('RECOVERED!');
  }
}

The index is 4 instead of 5 because JavaScript index is 0-based.

Place this in cell E19

=ARRAYFORMULA(if(ISBLANK(C19:C29),, VLOOKUP(C19:C29,Rates!B2:C24,2,False)))

It will look up the values from the Rates sheet and apply them, and do this for rows 19-29. If there is no value in column C it will return no value.

The Arrayformula and use of array ranges, C19:C29 applies this to every row in that range. The IF test the cell in column C. If it is blank, the test returns TRUE. Since we have ,, taking us past the IS TRUE portion of the IF statement, we return no value when cell C is empty. When cell C has a value, we look that up in the Rates sheet via VLOOKUP() and return the second column of the range we search. The FALSE for VLOOKUP means the data does not have to be sorted so it looks for an exact match as opposed to the first closest match.