It might be more complicated than that. At the moment the Rate Table is in a block diagonal form suggesting you add a new set of Work Descriptions for each Variety. If that is the case you may well finish up with duplicated Work Descriptions applying to different Varieties.
The alternative is that you identify common Work Descriptions but that means that, for a given Variety, the Rates will be scattered over the column. If that is the case it would be better to sort the Work Descriptions by weight.
In the case of the first strategy, you would first need to identify the correct column in the Rate Table, defining 'RateList' (say) = INDEX( RateTable, 0, MATCH(Table1[@VARIETY], Variety, 0 ) )
and only then use the Work Description, but filtering out those entries that do not apply to the chosen Variety = INDEX( RateList, MATCH( [@[WORK DESCRIPTION]], IF( RateList>0, WorkDescription, "" ), 0 ) )
If, on the other hand, you can guarantee that the Work Descriptions are unique, then @GraH - Guido's suggestion will work. Using my structured references for comparison purposes, that would be = INDEX( RateTable, MATCH( [@[WORK DESCRIPTION]], WorkDescription, 0 ), MATCH(Table1[@VARIETY], Variety, 0 ) )
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.