Vlookup within VBA - table on different sheet?

Is it possible to do a Vlookup within VBA that has a table that isn't on the ActiveSheet? I'm trying to write a function that looks up over one range if the value in a column is "G" and a different range if the value is "Y". The table is on a different sheet than those values, and I keep getting #VALUE? as the result of the function.

Also, some of the values that my vlookup is dependent on are also results of another vlookup. Whenever I change the original source, I get #N/A in all of my formulas until I do something like hit F2 on a cell on that sheet, and press Enter. I have Application.Volatile in both of the user functions I'm using now. Is there something else I could do so they update automatically?

On the data sheet, I usually select all the data I need and then name the range using the Name Box usually located right above column A. Then named range can be whole columns (C:D). On the second sheet your vlookup can reference the named range -

=VLOOKUP(A1,vendors,2,FALSE) [assuming your named range is Vendors and the value you want to lookup is in cell A1]

The if(isna(vlookup... can be used to account for missing values.

On the data sheet, I usually select all the data I need and then name the range using the Name Box usually located right above column A. Then named range can be whole columns (C:D). On the second sheet your vlookup can reference the named range -

=VLOOKUP(A1,vendors,2,FALSE) [assuming your named range is Vendors and the value you want to lookup is in cell A1]

The if(isna(vlookup... can be used to account for missing values.


Thank you. Naming the ranges allows them to be accessed normally. I still have that problem with the function not updating properly, but I'll keep working on it.