Originally Posted by ArviLaanemets

a) Use absolute references for account reference table. P.e. you have on separate sheet Accounts a table with headers Account and NewAccount in A1 and B1, and data filled down to row 600. Now when on some another sheet with account numbers in column A you want to get new account into some other colum on row 2, the formula will be:
=VLOOKUP($A2,Accounts!$A$2:$B$600,2,0)

b) Define all tables as Tables (select any single cell in datarange; Insert>Table; check "My table has headers"; name the defined table). P.e. with table tAccounts defined on sheet Accounts, and calculating new account number in another defined Table with old account in column AccountNo, for any Table row the formula will be:
=VLOOKUP([@AccountNo],tAccounts,2,0).

Both formulas on coping down adjust the reference to old account, but leave the reference to accounts table unchanged.