You don't bother with the worksheet name you just give unique names to each of your arrays.
So for example if your first array is on Worksheet1 you can call it Array1 and if it is on Worksheet2 you can call it Array2. On each worksheet you can have multiple arrays and as long as you keep the array names unique you won't have a problem.
Then you use:
=VLOOKUP(G17,Array1,6,FALSE) instead of =VLOOKUP(G17,'worksheet'!$A$9:$I$400,6,FALSE)
Roaminoz is correct that you need to combine it with an IF statement if you want it look up different arrays based on the outcome of your formula. If (say) you want it to use Array1 if the outcome is less than 10 and Array2 if it is greater than or equal to 10 then you do this:
