5 Excel Functions Every Tax Preparer Should Know — With Real Workpaper Examples

TL;DR: You know SUM. You know VLOOKUP. But you’re still doing things manually that Excel can do in one formula — rolling up trial balance categories, mapping accounts across years, computing period-end dates, wrapping errors so your workpaper doesn’t light up with #N/A, and formatting account codes that lose their leading zeros. These five functions each solve one of those problems. Learn them once, use them every day.

#How This Post Works

Each section covers one function:

• The syntax — with a plain-English translation, not Microsoft documentation
• A tax workpaper example — a specific scenario you’ll recognize
• Common mistakes — the things that go wrong and how to fix them
• When to use it vs. an alternative — because every function has trade-offs

These aren’t generic “Excel function guides.” They’re written for someone staring at a trial balance, a depreciation schedule, or an account mapping table — with a deadline.

---

#1. SUMIFS — Multi-Condition Summing

#The Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Plain English: “Add up everything in column D where column A equals X AND column B equals Y AND column C is greater than Z.”

SUMIFS is SUMIF with multiple conditions. The order matters: the first argument is the column you’re summing, then pairs of (range, criteria). You can chain as many condition pairs as you need.

#Tax Workpaper Example: Department-Level Trial Balance Roll-Up

You have a trial balance with columns A–D:

Account	Department	Account Name	CY Balance
40010	Audit	Audit Revenue	$425,000
40020	Tax	Tax Revenue	$612,000
40030	Consulting	Consulting Revenue	$198,000
50010	Audit	Salaries-Audit	$287,000
50020	Tax	Salaries-Tax	$341,000
50030	Consulting	Salaries-Consult	$142,000

The partner wants a one-page summary: revenue and salaries by department. You could filter, copy, paste three times. Or:

Revenue by department:
=SUMIFS(D:D, B:B, "Audit", A:A, ">=40000", A:A, "<=49999")
=SUMIFS(D:D, B:B, "Tax", A:A, ">=40000", A:A, "<=49999")
=SUMIFS(D:D, B:B, "Consulting", A:A, ">=40000", A:A, "<=49999")

Now replace the department name with a cell reference ($F2 instead of "Audit") and you can drag the formula across all three departments. One formula, no filtering, no manual summing, no copy-paste mistakes.

Account-range trick: Instead of hard-coding >=40000 and <=49999, put a mapping table on a separate sheet:

Dept	Acct Start	Acct End
Audit	40000	49999
Tax	50000	59999
Consulting	60000	69999

Then your formula becomes:

=SUMIFS(D:D, B:B, $F2, A:A, ">="&G2, A:A, "<="&H2)

Now when the firm reorganizes account codes next year, you change the mapping table — not 47 formulas.

#Common Mistakes

Criteria order. =SUMIFS(D:D, "Audit", B:B) — wrong. The sum range comes first in SUMIFS (unlike SUMIF, where it comes last). If your formula returns zero and you know there’s data, swap the first argument.

Text vs. numbers. If your account codes are stored as text (left-aligned, green triangle), >=40000 won’t match them because Excel compares text alphabetically, not numerically. Convert text-numbers to actual numbers first (Text to Columns, or the Text-to-Numbers macro).

Blank criteria cells. If your criteria cell is blank, SUMIFS matches every row. This is useful for “show all departments” — leave the department cell blank. But it’s dangerous if you accidentally clear a criteria cell and suddenly your revenue roll-up includes tax, audit, consulting, and the mailroom budget.

#When to Use SUMIFS vs. PivotTable

SUMIFS	PivotTable
Formula updates instantly	Must refresh manually
Survives row insertions (with Tables)	Range can shrink after refresh
Auditable — you can trace precedents	Hard to audit — “where did this number come from?”
One formula per cell	One PivotTable does it all
Gets unwieldy with 5+ criteria	Handles 5+ criteria easily

Rule of thumb: Use SUMIFS for dashboards and summary schedules that the partner will review. Use PivotTables for exploratory analysis. Most tax workpapers benefit from both — the PivotTable to find the pattern, the SUMIFS to lock it into the deliverable.

---

#2. XLOOKUP — The VLOOKUP Replacement You Should Have Switched to in 2020

#The Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Plain English: “Find this value in column A. Give me what’s in the same row in column D. If you don’t find it, show this instead.”

XLOOKUP replaces VLOOKUP, HLOOKUP, and INDEX-MATCH in a single function. It looks left, right, up, or down — no column-index counting required. It handles errors with a built-in if_not_found argument instead of forcing you to wrap everything in IFERROR.

#Tax Workpaper Example: Account Code Mapping Across Years

Your firm changed the chart of accounts between 2025 and 2026. Account 40010 (“Audit Revenue”) became 41010 (“Professional Services — Audit”). You need to map every 2025 account to its 2026 equivalent for the carryforward schedule.

You have a mapping table:

Old Code	New Code	Old Name	New Name
40010	41010	Audit Revenue	Prof Svcs-Audit
40020	41020	Tax Revenue	Prof Svcs-Tax
50010	51010	Salaries-Audit	Compensation-Audit

VLOOKUP approach:

=VLOOKUP(A2, Mapping!A:D, 2, FALSE)

Problems: (1) the lookup column must be the first column in the range, (2) you count column numbers (which break when someone inserts a column in the mapping table), (3) if the account code isn’t found, you get #N/A and need to wrap in IFERROR.

XLOOKUP approach:

=XLOOKUP(A2, Mapping!A:A, Mapping!B:B, "Not mapped")

The lookup column (A) and return column (B) are separate — no column counting. The "Not mapped" argument handles missing accounts without a separate IFERROR wrapper. If someone inserts a column in the mapping table, the formula still works because it references specific columns, not an index number.

#Why Left-Lookup Matters for Tax Work

VLOOKUP can only look to the right — the return column must be to the right of the lookup column. XLOOKUP can look in any direction.

Tax scenario: you have a list of vendor EINs in column C and need the vendor name from column A. With VLOOKUP, you’d need to rearrange the columns or use INDEX-MATCH. With XLOOKUP:

=XLOOKUP(C2, C:C, A:A)

One formula. No column rearrangement. Works instantly.

#Common Mistakes

XLOOKUP returning the wrong row. This happens when lookup_array and return_array have different sizes. If lookup_array is A1:A100 but return_array is B1:B50, XLOOKUP matches row 60 in column A against row 60 in column B — which is empty. Always use whole-column references (A:A, B:B) or matched-size ranges.

Default match mode is exact match. Unlike VLOOKUP (where you must specify FALSE for exact match), XLOOKUP defaults to exact match. Omitting the match_mode argument works the way you expect. But if you want approximate match (for tiered tax rates, for example), set match_mode to -1 or 1.

Not available in older Excel. XLOOKUP requires Excel 2021 or Microsoft 365. If you’re sharing workbooks with preparers on Excel 2019 or earlier, use INDEX-MATCH instead — it works in every version since 2007.

#When to Use XLOOKUP vs. INDEX-MATCH

XLOOKUP	INDEX-MATCH
Excel 2021+ / Microsoft 365	All Excel versions
One function, simple syntax	Two functions, more typing
Built-in if-not-found	Must wrap in IFERROR/IFNA
Cannot do two-way lookup (row + column)	Two-way lookup with MATCH on both axes
Vertical OR horizontal	Vertical AND horizontal combined

Rule of thumb: If everyone at your firm is on Microsoft 365 or Excel 2021+, use XLOOKUP for all single-axis lookups. Keep INDEX-MATCH for two-way lookups (matrix-style tax rate tables, depreciation convention lookups). If your firm has mixed Excel versions, INDEX-MATCH is the safer choice.

---

#3. EOMONTH — Period-End Dates Without the Mental Math

#The Syntax

=EOMONTH(start_date, months)

Plain English: “Give me the last day of the month that’s X months after this date.”

months can be positive (future), negative (past), or zero (end of the current month). =EOMONTH("6/15/2026", 0) returns 6/30/2026. =EOMONTH("6/15/2026", 6) returns 12/31/2026. =EOMONTH("6/15/2026", -1) returns 5/31/2026.

#Tax Workpaper Example 1: MACRS Mid-Quarter Convention Dates

You’re building a fixed asset depreciation schedule. A machine was placed in service on March 12, 2026. Under MACRS mid-quarter convention (asset placed in service in Q1), depreciation starts at the midpoint of Q1 — February 15. But you need the period-end dates for each year’s depreciation calculation.

Instead of mentally calculating “March plus 9 months is December… no, wait, mid-quarter… February 15 plus…”:

Formula	Result	What It Means
=EOMONTH(DATE(2026,2,15), 0)	2/28/2026	Year 1 through end of Q1
=EOMONTH(DATE(2026,2,15), 10)	12/31/2026	Year 1 through year-end
=EOMONTH(DATE(2026,2,15), 22)	12/31/2027	Year 2 through year-end
=EOMONTH(DATE(2026,2,15), 34)	12/31/2028	Year 3 through year-end

Each formula computes the exact number of months in the depreciation period without you touching a calendar. For a 50-asset schedule, this replaces 50 manual date calculations.

#Tax Workpaper Example 2: Accrual Cutoff Dates

A client’s fiscal year ends January 31. You need to accrue December revenue (for the FYE 1/31/2027 tax return) and test that accrual against January cash receipts.

December accrual period-end: =EOMONTH(DATE(2026,12,1), 0)  → 12/31/2026
Fiscal year-end:             =EOMONTH(DATE(2027,1,1), 0)  → 1/31/2027
Prior fiscal year-end:       =EOMONTH(DATE(2027,1,1), -12) → 1/31/2026

Now your accrual workpaper auto-generates all period-end dates from a single fiscal-year-end input. Change one cell (the FYE date), and every date in the workpaper updates.

#Tax Workpaper Example 3: Quarterly Estimate Due Dates

Estimated tax payment due dates follow a pattern: 4/15, 6/15, 9/15, 1/15 (following year). With EOMONTH, you can generate the full year from the calendar year:

Quarter	Formula	Due Date
Q1	=DATE(A1,4,15)	4/15/2026
Q2	=DATE(A1,6,15)	6/15/2026
Q3	=DATE(A1,9,15)	9/15/2026
Q4	=DATE(A1+1,1,15)	1/15/2027

Where A1 contains the tax year (2026). Now the partner can change A1 and every estimate due date in the workpaper shifts to the new year.

#Common Mistakes

Forgetting that EOMONTH returns the END of the month. =EOMONTH("1/15/2026", 1) returns 2/28/2026, not 2/15/2026. If you need the same day of the month, use EDATE instead: =EDATE("1/15/2026", 1) returns 2/15/2026.

Using text dates instead of date serial numbers. =EOMONTH("January 15 2026", 0) works in most cases, but =EOMONTH(A2, 0) where A2 contains text (not a date value) returns #VALUE!. Always verify that your date column is formatted as Date, not Text.

Fiscal years that don’t align with calendar months. If a client’s fiscal year ends on a non-month-end date (e.g., the last Friday in January), EOMONTH won’t compute it directly. Use WORKDAY.INTL or a lookup table instead.

---

#4. IFERROR — Catch Errors Without Killing Your Workpaper

#The Syntax

=IFERROR(formula, value_if_error)

Plain English: “Try this formula. If it produces an error, show this instead. If it works, show the result.”

IFERROR wraps any formula and catches ALL errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!. For better or worse — it catches everything.

#Tax Workpaper Example 1: Clean Lookups in a Summary Schedule

You have a trial balance with 487 rows and a summary schedule that pulls totals via XLOOKUP for 20 line items. Some line items don’t exist for this client (e.g., no Cost of Goods Sold for a service business). Without error handling, those cells show #N/A — and the partner won’t sign a workpaper with visible errors, even if they’re “expected.”

With IFERROR:

=IFERROR(XLOOKUP(A2, TB!A:A, TB!D:D), 0)

Line items with no TB entries show $0 instead of #N/A. The partner sees zeros where there’s no activity — which is correct. The workpaper looks clean because the output accurately represents the data: no entry = no balance.

#Tax Workpaper Example 2: Ratio Analysis with Potential Zero Denominators

You’re computing the debt-to-equity ratio for five related entities. One entity has negative equity (accumulated losses exceed contributed capital). Without error handling:

=C12/D12     → #DIV/0! (when D12 = 0)

With IFERROR:

=IFERROR(C12/D12, "N/M")

The cell shows “N/M” (not meaningful) instead of #DIV/0!. More importantly, it doesn’t break downstream formulas that reference this cell (a #DIV/0! cascades through every formula that depends on it).

#The IFERROR Trap (Read This Before You Wrap Everything)

IFERROR catches ALL errors. This is its strength and its danger.

Good use of IFERROR: Wrapping a VLOOKUP or XLOOKUP where you know the expected error is #N/A (value not found). You want to replace it with 0, "Not found", or "".

Bad use of IFERROR: Wrapping a formula that references another sheet and getting "" instead of #REF!. A #REF! means someone deleted the sheet your formula references. IFERROR hiding that #REF! means your workpaper shows "" (blank) where the number should be — and you’ll never know the data is missing.

The safe alternative: IFNA

=IFNA(XLOOKUP(A2, TB!A:A, TB!D:D), 0)

IFNA ONLY catches #N/A errors. If your XLOOKUP returns #REF! because the lookup array was deleted, IFNA lets that error through — which is what you want. You NEED to see #REF! errors. You DON’T need to see #N/A from missing lookups.

Rule of thumb: Use IFNA for lookup formulas. Use IFERROR for division (formulas that might legitimately divide by zero). Never use IFERROR to wrap an entire workpaper formula without understanding what errors it could produce.

#Common Mistakes

IFERROR(0/0, 0) hides data integrity issues. If your formula references a cell that contains #REF!, wrapping it in IFERROR doesn’t fix the #REF! — it hides it. The workpaper looks clean. The numbers are wrong. This is the #1 cause of “I don’t understand why the return doesn’t foot” during review.

Wrapping too much. =IFERROR(VLOOKUP(A2,Data!A:Z,3,FALSE)+VLOOKUP(A2,Data!A:Z,4,FALSE), 0) hides errors from BOTH lookups. If the first lookup fails and the second succeeds, you get zero instead of the second lookup’s result. Wrap EACH lookup individually:

=IFERROR(VLOOKUP(A2,Data!A:Z,3,FALSE),0) + IFERROR(VLOOKUP(A2,Data!A:Z,4,FALSE),0)

Now each lookup fails independently and you get the correct sum for the ones that succeed.

---

#5. TEXT — Format Numbers Without Changing Their Values

#The Syntax

=TEXT(value, format_text)

Plain English: “Take this number and show it with this format — as a text string, not a number.”

The format_text argument uses the same codes as Excel’s Format Cells dialog: "00000" = five digits with leading zeros, "$#,##0.00" = currency, "mm/dd/yyyy" = date, "0.0%" = percentage with one decimal.

#Tax Workpaper Example 1: Five-Digit Account Codes with Leading Zeros

Your ERP exports account codes as numbers. Account 04010 (a balance sheet account) exports as 4010 — Excel drops the leading zero because it treats the value as a number. When you upload the workpaper back to the ERP or use the account code as a lookup key, 4010 doesn’t match 04010.

=TEXT(A2, "00000")

• 4010 → "04010"
• 50010 → "50010"
• 100 → "00100"

Now your account codes match the ERP and your lookups work. This is the difference between a VLOOKUP that returns #N/A on 40% of rows and one that works on 100%.

#Tax Workpaper Example 2: Date Labels for Period-Over-Period Headers

You have a column of dates (months) and need to create a header that reads “January 2026 vs. January 2025.”

="CY "&TEXT(A2, "mmmm yyyy")&" vs. PY "&TEXT(EDATE(A2,-12), "mmmm yyyy")

Result: "CY January 2026 vs. PY January 2025". Change A2 and the entire header updates — no manual text editing across 12 columns.

#Tax Workpaper Example 3: Formatted Amounts in Narrative Footnotes

The tax return requires a footnote: “The corporation recognized $247,500 of §179 expense on qualified improvement property placed in service during 2026.”

The $247,500 comes from cell C47 on the fixed asset schedule. You want the footnote to update when the number changes:

="The corporation recognized "&TEXT(C47,"$#,##0")&" of §179 expense on qualified improvement property placed in service during "&TEXT(A1,"yyyy")&"."

When C47 changes from $247,500 to $312,800, the footnote updates. When A1 changes from 2026 to 2027, the year updates. One linked footnote replaces manual text editing across a 40-page return.

#Tax Workpaper Example 4: Concatenating Account Code + Name for Unique Keys

You need a unique identifier for each TB row that combines the account code and department:

=TEXT(A2, "00000")&"-"&B2

Result: "04010-Audit", "50010-Consulting". This creates a key you can use for lookups, deduplication, and cross-referencing between sheets.

#Common Mistakes

TEXT converts numbers to text. After =TEXT(1234, "00000"), the cell contains the text string "01234", not the number 1234. If you reference this cell in a SUM formula, it returns zero because SUM ignores text. Use TEXT for display and key-building, not for cells you plan to sum or average.

Format codes are case-sensitive in some contexts. "MM/DD/YYYY" and "mm/dd/yyyy" both produce month/day/year, but "MMMM" produces “January” while "mmmm" also produces “January.” However, "DDDD" produces the full day name (“Monday”) while "dddd" does the same. In practice, case doesn’t matter for date formatting in Excel’s TEXT function, but "M" (month) and "m" (minute) conflict in time formatting — use "M" for month and "m" for minutes to avoid ambiguity.

Forgetting TEXT when building formula-driven strings. ="FYE "&A2 where A2 contains a date returns "FYE 45658" (the date serial number) instead of "FYE 1/31/2026". Always wrap dates in TEXT: ="FYE "&TEXT(A2,"m/d/yyyy").

---

#Putting Them Together: A Real Workpaper Dashboard

Here’s what the five functions look like in a single summary dashboard — the kind you’d build for the partner to review in 30 seconds instead of drilling into 487 TB rows.

Formula	Function	What It Does
=SUMIFS(TB!D:D, TB!B:B, $A2, TB!A:A, ">=40000", TB!A:A, "<=49999")	SUMIFS	Rolls up revenue by department
=XLOOKUP($A2, Mapping!A:A, Mapping!C:C, "New code needed")	XLOOKUP	Maps old account to new account name
=EOMONTH($B$1, 0)	EOMONTH	Computes period-end from a single date input
=IFNA(XLOOKUP($A2, PY!A:A, PY!D:D), 0)	IFNA	Retrieves PY balance, returns zero if new account
=TEXT($A2, "00000")&"-"&$B2	TEXT	Builds unique key for cross-referencing

Cell $B$1 contains the period-end date (12/31/2026). Cell $A2 contains the account code. Change B1 and every date-based formula updates. Drag the formulas down 100 rows and the dashboard covers every account in the TB. This is the difference between “I updated the workpaper” and “the workpaper updates itself.”

#Adapt It for Your Firm