Excel VLOOKUP from a different Sheet

Question

Although there have been numerous discussions about this both here and elsewhere, I tend to encounter problems with every approach I take.

With the help of the corresponding value from a list of values, I'm attempting to fill up a cell next to it with a value. A list of Test -> Action pairings, for instance, is organized into two columns. I want to enter a value in one cell that already appears in column B, and then I want to enter the corresponding value in column A in the cell that appears next to it. 

Here's my Sheet.

The columns "Actions" and "Tests (test groups)" define the corresponding values.

When I enter a value in the "Action" value, I populate the cells in the "Test (test group)" column (behind the first two columns, from row 10 and down) using VLOOKUP. Easy enough.

But now I require this precise feature on a different sheet. Rows 10 and down of the "Action" and "Test (test group)" columns need to be moved to another sheet while the data in this sheet must still be referenced (rows 2 - 6).

In the cell where I use VLOOKUP, all of the other options I've tested, including INDIRECT, give me either "#REF" or "#VALUE."

Is anyone able to explain how to do this, related to this example?

Answer 1

The worksheet name must be included in your formula. If the name contains spaces or other special characters, only use single quotations. akin to this

=VLOOKUP(A1,'sheet-name-with-dash'!$A$1:$B$9,2,FALSE)

I hope this helps you.