Lesson 5: VLOOKUP and XLOOKUP with a chart of accounts

Practice exercises for accountants, checked instantly in your browser.

A GL export often shows only account numbers. =VLOOKUP(A2,$F$2:$H$8,2,FALSE) finds A2 in the first column of the chart of accounts and returns the value from column 2 of that table. The last argument, FALSE, forces an exact match. Leave it out and VLOOKUP does an approximate match, which on an unsorted chart of accounts quietly returns the wrong account.

XLOOKUP(value, lookup column, return column, if not found) does the same job with separate columns and a built-in fallback. On older Excel, wrap VLOOKUP in IFERROR to show a message instead of #N/A.

Worked example: =VLOOKUP(1010,$F$2:$H$8,3,FALSE)
Finds account 1010 and returns its type (column 3 of the table): Asset.

Exercises

Exercise 1

Harbor & Pine Consulting's chart of accounts is in F1:H8. In C2, return the account name for the number in A2. Fill to C7.

Exercise 2

In D2, return the account type for A2 and fill to D7.

Exercise 3

A new export includes account 6999, which is not in the chart. In C2, return the account name, or Not found when there is no match. Fill to C5.