Here is the complete answer(s).

I think you main problem is how to use a name rane in VBA. Try this

Range("TOTATL_POPULATION")

Total population is a NAME in the workshet

1) If you are using VBa it is more efficient to use FIND the using a
worksheet function. Here is how to do it in VBA efficiently

with sheets("Sheet2")
set c = Range("TOTATL_POPULATION").find(what:=.Range("A2") , _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
ReturnValue = c.offset(0,1)
else
msgbox("did not find : " & .Range("A2"))
end if
end with

2) Using VLOOKUP()

VLOOKUP need as string just like it would appear in the worksheet

ReturnValue = worksheetfunction.vlookup("A2, B2, 2, FALSE")

or
lookupvalue = Range("A2")
ReturnValue = worksheetfunction.vlookup(lookupvalue, _
Range("TOTATL_POPULATION") , 2, FALSE)

or
lookupvalue = Range("A2")
ReturnValue = worksheetfunction.vlookup(lookupvalue & _
", B2 , 2, FALSE")


3) The formula solution

Range("Z100").formula = "=vlookup(A2, B2, 2, FALSE)"

or

lookupvalue = Range("A2")
Range("Z100").formula = "=vlookup(" & lookupvalue & ", B2, 2, FALSE)"


Notice the formula is a string
lookupvalue = Range("A2")
formulastring = "=vlookup(" & lookupvalue & ", B2, 2, FALSE)"
Range("Z100").formula = formulastring


I have a question:
Here is what I want to happen. I am using UserForm/TextBoxes in transfer
Argument 1 and Argument 2 in a Row 2 Sheet 2. And then I need to use in
VLOOKUP fucntion, as Argument 1 should be as a LOOKUP_VALUE and Argument 2 as
TABLE_ARRAY. Both or arguments should be dynamic, i.e. the actual data is
tracked in other sheet in the same workbook.
I will try to make it more clear by following: I have already a UserForm
that copies
two variables from Sheet1 to position of A2 and B2 in Sheet2. In Sheet2, in
cell C3,
I need to place a VLOOKUP formula so it reads cell B2 as dynamic table_array.
Since the 1st UserForm every time executes CommandButton it copies different
value. For instance, it may come as "TOTAL_POPULATION" (this has to be an
table_array, pre-determined in Sheet3 and broke according to years) and the
second variable as "Cherokee, KS". The function has to find "Cherokee, KS" in
"TOTAL_POPULATION" table_array which is already in Sheet3 and return value
for 2000 year (that would be =VLOOKUP(A2, B2, 2, FALSE).
The reason I wanted to use UserForm, by inserting formula, it copies it as
and "B2" and not the
table_array. And there are about 20 table_arrays, so, that the 1st UserForm
is used, a user has an option of selecting any other than "TOTATL_POPULATION"
and so one. But it doesn't! I tried to use INDIRECT, but seems like it is
not what I need for my needs. CLEAN and TRIM commands were used in VBA for
(Trim(TextBox2.Text)) to make sure there are no blanks in
between. But somehow, I keep getting the "#N/A" error. Meanwhile, if I type
"TOTAL_POPULATION_FORECAST" table_array, it works perfect!