How to look up account names in Excel

Ledger exports often carry only an account number, such as 5200, and leave the name to a separate chart of accounts. An exact-match lookup fills the names in for you, line by line. Here is how to do it, what to do when the number sits in the wrong column, and how to catch numbers that aren't in the chart at all.

Why account numbers need a lookup

A chart of accounts is the master list of a business's accounts. Each account has a number, a name and a type such as Asset or Expense. Software stores transactions against the number because numbers are short and never misspelled. People, however, read names. To review a ledger, total it by name or build a report, you first need the name next to each line.

Typing the names in by hand is slow and invites errors. A lookup formula takes the number on each line, finds it in the chart, and returns the name from the same row. Fill it down once and every line is labeled. If the chart changes, the labels follow.

If the account types themselves are new to you, chart of accounts classification explains what Asset, Liability, Equity, Revenue and Expense mean. This page is about the spreadsheet side: getting the right row back every time.

The MO-220 objective this covers

This lesson is MO-220 practice for "Prepare a trial balance", under "Organize financial data with formulas", which includes using lookup functions on accounting data. MO-220 task instructions describe the purpose, such as "show the account name for each line", rather than naming a lookup function. Part of the skill is spotting the details that decide which approach works: is the chart sorted, and which column holds the number? Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: names from account numbers

Juniper Landscaping's March 2026 ledger has 16 lines in rows 4 to 19, with the date in A, the account number in B and the amount in C. The names go in column D. The chart of accounts sits to the right in H4:J13:

Notice that the numbers are not in order. That one detail decides how the lookup must be written.

Step 1: write an exact-match lookup

In D4, the solution uses XLOOKUP:

=XLOOKUP(B4,$H$4:$H$13,$I$4:$I$13)

It takes three arguments: the value to find (the number in B4), where to find it (the chart's number column H) and what to return (the name column I). XLOOKUP looks for an exact match unless you ask otherwise, so the order of the chart doesn't matter.

The older VLOOKUP also works, as long as you ask for an exact match with FALSE (or 0) as the last argument: =VLOOKUP(B4,$H$4:$J$13,2,FALSE). The 2 means "the second column of the table", which is the name.

Step 2: lock the chart and fill down

The chart range has dollar signs so it stays on H4:J13 as you fill down to D19. Only B4 moves, to B5, B6 and so on. Here are the first eight results:

Spot-check one: row 4 has 5200. In the chart, 5200 is on row 12, and I12 says Utilities Expense. That matches D4.

When the number is in the last column

The second exercise, for Summit Bike Repair, uses a chart laid out the other way round: name in H, type in I and account number in J, the last column. You need the name in column D and the type in column E.

VLOOKUP can't do this. It always searches the first column of its table and returns a column to the right, and here the number is on the far right. The solution splits the job in two:

• MATCH finds the position of the number in column J. MATCH(B4,$J$4:$J$13,0) returns 5 for account 1010, because 1010 is the fifth number in J4:J13. The 0 asks for an exact match.
• INDEX returns the value at a given position in another column. INDEX($H$4:$H$13,5) returns the fifth name, Cash.

Put together, in D4 and E4:

=INDEX($H$4:$H$13,MATCH(B4,$J$4:$J$13,0))

=INDEX($I$4:$I$13,MATCH(B4,$J$4:$J$13,0))

The two formulas are identical except for the column INDEX returns from. For the first line, account 1010, D4 shows Cash and E4 shows Asset. XLOOKUP would also work here, since it can return a column on either side: =XLOOKUP(B4,$J$4:$J$13,$H$4:$H$13). The exercise checks the results, not which function produced them.

Catching numbers that are not in the chart

Real ledgers contain mistakes. A line might be posted to 5120 when the chart only has 5100. A plain lookup can't find it and returns the error #N/A. One error is useful information, but it also breaks any total that includes the column.

The third exercise, for Copperleaf Studio, asks for the text Not in chart instead. XLOOKUP has a fourth argument for exactly this, the value to return when nothing is found:

=XLOOKUP(B4,$H$4:$H$13,$I$4:$I$13,"Not in chart")

With other lookups, wrap the formula in IFERROR, which returns its second argument whenever the first one is an error: =IFERROR(VLOOKUP(B4,$H$4:$J$13,2,FALSE),"Not in chart").

Three of Copperleaf's 16 lines use numbers missing from the chart: 5120 (1,377.55), 1710 (301.42) and 4100 (1,833.89). The task then asks how many lines are affected and how much money they carry:

=COUNTIF(D4:D19,"Not in chart") in G1 returns 3.

=SUMIF(D4:D19,"Not in chart",C4:C19) in G2 returns 3,512.86.

Those two cells turn a list of labels into a summary someone can act on: three lines, 3,512.86 in total, to be reclassified before the trial balance is prepared.

Common mistakes

Approximate match on an unsorted chart

If VLOOKUP ends with TRUE, or the match argument is left out, the function assumes the first column is sorted in ascending order. It then searches by jumping around the list rather than checking each row. On an unsorted chart it can stop at the wrong row and return a neighboring account. The worst part is that the result looks plausible: a real account name, just the wrong one. Ask for an exact match: FALSE or 0 in VLOOKUP, or 0 as MATCH's match type.

Returning the type instead of the name

In VLOOKUP, the column number counts from the left edge of the table. With the table H4:J13, column 2 is the name and column 3 is the type. Using 3 fills column D with Expense, Asset and Liability instead of account names.

Using MATCH without an exact match

MATCH defaults to approximate matching, which has the same sorted-data assumption as VLOOKUP with TRUE. On Summit Bike Repair's unsorted column J, some positions come back wrong, so the name and type belong to a different account. Always add 0 as the third argument.

Swapping the name and type columns

In the second chart, the name is in H and the type in I. That is the opposite of the first chart, which is easy to miss. If column D shows Asset and column E shows Cash, the two INDEX ranges are the wrong way round.

Leaving #N/A on missing numbers

Without a fallback, Copperleaf's three unknown numbers show #N/A. The task asks for the text Not in chart, which is also what the count and total in G1 and G2 look for.

Totaling every line in G2

G2 should total only the lines not in the chart. =SUM(C4:C19) returns 28,854.28, the whole ledger, instead of 3,512.86. The condition belongs in a SUMIF.

Where this fits

Lookups are the bridge between raw exports and readable reports. Once every line has a name and a type, you can total by account with conditional sums (see SUMIFS totals by account) and assemble a full trial balance in Excel. The type column also drives the next step, deciding each account's normal balance side, covered in normal balances with IF.