5 Excel Formulas Every Accountant Should Know (With Examples)

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. For accountants, that usually means matching an ID, code, or invoice number to a name, category, or amount from a source table.

A common accounting use case is enriching an export before review. If your general ledger export has vendor IDs but not vendor names, VLOOKUP can pull the names from a master vendor list so the file becomes usable for close and variance analysis. Use exact match, not approximate match, for finance data unless you intentionally work with sorted ranges such as tax bands.

SUMIF adds values only when a condition is met. It is one of the fastest ways to answer targeted finance questions without building a pivot table first.

In accounting, SUMIF is useful when you need a quick subtotal for one account, one department, or one expense category. For example, you can total all transactions coded to travel, sum all open items for a single customer, or isolate one cost center during flux analysis. It is especially handy when someone asks for a number immediately and you do not want to restructure the whole workbook.

IFERROR lets you replace formula errors with a cleaner fallback value. The formula itself does not fix the underlying issue, but it keeps reports readable while you identify which rows need attention.

That matters in accounting because lookup failures are common when source data is incomplete or codes are inconsistent. Wrapping a lookup in IFERROR can prevent a report from filling with #N/A values right before review. The safe habit is to use a visible fallback like "Missing" or a dash instead of 0 when zero could be mistaken for a real balance.

INDEX/MATCH is a more flexible alternative to VLOOKUP. MATCH finds the row number of a value, and INDEX returns the result from the column you actually want.

For accountants, the biggest benefit is that the return column does not have to sit to the right of the lookup column. That helps when you inherit messy workbooks or standardized reports where the key field is not placed conveniently. If you need to pull an FX rate, cost center owner, or account description from a structured but awkward table, INDEX/MATCH is often the safer option.

XLOOKUP is the modern lookup formula in Excel and, when available, it is usually the best default choice. It can look left or right, uses clearer arguments, and handles missing values without needing IFERROR in many cases.

An accounting use case is replacing older VLOOKUP files that break whenever a new column gets inserted. Because XLOOKUP points directly to the lookup array and return array, it is more resilient during recurring close workbooks. If your team uses a current version of Excel, this is often the cleanest way to match records across billing, payroll, and reporting exports.