Excel: VLOOKUP from a different Sheet

1
votes

I know there's several posts about this, here and other places, but I seem to get errors no matter which method I try.

I'm trying to fill an adjacent cell with a value based on the corresponding value from a list of values. For example, there's a list of Test -> Action pairs, defined in two columns. In another cell, I want to type in a value which exists in column B, and then fill the adjacent cell with the corresponding value in column A.

Here's my Sheet.

enter image description here

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

I'm using VLOOKUP in the "Test (test grop)" column (below the first two columns, from row 10 and down), to fill the cells when I'm entering a value in the "Action" value. Simple enough.

However, now I need this exact functionality in another sheet. I need to move the "Action" and "Test (test group)" columns - row 10 and down - to another sheet, and still reference the values in this sheet (row 2 - 6).

I've tried INDIRECT and a couple of other alternatives, and all give me either "#REF" or "#VALUE" in the cell where I use VLOOKUP.

Anyone able to explain how to do this, related to this example?

2
excelvlookup
just update the table reference to sheet1!Range - Sunny Sharma

2 Answers

3
votes

You need to add the worksheet name to your formula. Use single quotes if you have spaces or special characters in the name. Like this:

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

may seem out of place, but whenever I see this kind of problems (in defining and using ranges) I think of the Excel option to define those ranges (like in Ctrl+F3, Name Manager).Showcase:

select your area: in your case A2:B6, 
hit: Ctrl+F3, 
name the range: i.e. LookupRange
use that range in Vlookup formula like: Vlookup($A12,LookupRange,2,0)

I do not use this on regular basis,but might get handy in a workbook with many sheets, ranges, formulas. Try this for fun at least. Hope it helps.