As to the ability to do a lookup on a different sheet, if you name the table
then the table can be on any sheet in the same workbook you don't have to
tell it where it's located (that's the "beauty" of naming a table).

If you want to maintain your "database" (table) in another book then the
syntax would be;

VLOOKUP($AwhateverRow,TableBook.xls!TestTbl,2,FALS E)

Notice the Book ("TableBook.xls!") is followed by an exclamation mark which,
in turn, is followed by the table name in that book.

Because you've told XL where to look it means that you could have an
identically name table in the recipient book as well.
(Handy for testing - when you're happy with it, just point the lookup to the
central book.)

Having your table in a separate book is a good way to do it as it separates
your table and allows for a separate maintenance procedure as far as
updating the data is concerned (ie you'd have one source for any application
and, if it has to be updated, then it only has to be changed in one place).

Now that I've bored you to death(!) I'll try and answer the second question.

I guess I didn't understand your original query. If you're wanting to
replace the value in your "Key" cell (the # that's used in your lookup
argument ("$AwhateverRow") ) the answer is that you can't, unless you use
VBA which may be beyond the scope here.
Now, having said that, I should offer the disclaimer that I'm not an expert
compared to some of the individuals here, but I doubt if it can be done
using the functions on a spreadsheet.
That is why I went into detail about the copy/paste-special after the lookup
is done. ie once the lookup retrieves the values, you can copy the results
and paste the values over the Key values, replacing them.

Hope this helps

--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.