I've got a Google Sheets workbook with two sheets:

CodePudding user response：

When using ARRAYFORMULA you won't be able to use QUERY in order to get the whole array of values as it will only return the first value that is found.

I created a formula that matches the value using VLOOKUP however I had to modify the name in Jobs from Standard to Standard 2.

This is the formula:

=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A,Rates!A2:D6,2,0)))

These are the results: