Using VLOOKUP in Accounting

Accountants use VLOOKUP to connect data. Instead of typing "Accounts Receivable" manually every time you see code 1100, you use VLOOKUP to pull it from your Master Chart of Accounts.

The Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

A Real-World Example

You have a Chart of Accounts in columns F and G:

Code (Col F)Account Name (Col G)
1000Cash
1100Accounts Receivable
2000Accounts Payable

If you have the code 1100 in cell A2 and want to find the name, your formula is:

=VLOOKUP(A2, $F$2:$G$4, 2, FALSE)

Three Rules for Accountants

  1. The Key must be in the first column: VLOOKUP only looks to the right. If your codes are in column G and names in F, VLOOKUP won't work (use INDEX/MATCH instead).
  2. Use Absolute References ($): Always lock your table_array (e.g., $F$2:$G$100). If you don't, the lookup table will "drift" as you drag the formula down.
  3. Always use FALSE: In accounting, "1101" is not "1100". If you omit the FALSE, Excel might give you the closest match instead of an error, which leads to wrong financial statements.

Common Mistakes

Practice using VLOOKUP with a real chart of accounts here.