With the new dynamic arrays there is an alternative to LOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH, namely, FILTER.
In the attached, I have first filtered the range 'RateTable' horizontally to select the relevant column 'RateFiltered'
(see sheet CONTRACTOR_ACCOUNT_FILTERS).
= FILTER( RateTable, ( Variety=ContractorAccountFilters[@VARIETY] ) )
I then find the specific rate by filtering the resultant column, selecting the row in which the Work Description matches and the value is not blank.
= FILTER( RateList, ( WorkDescription = [@[WORK DESCRIPTION]] ) * ( RateList>0 ) )
Note that the second formula calls the first for evaluation twice.