Free Resource
5 formulas accounting teams use most — with syntax, examples, and tips.
Email copy
Save this reference for close week and get occasional accounting Excel tips from GridSchool.
Find a single precise record — vendor ID, account code, employee number.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, 0)Example
=VLOOKUP(A2, Vendors!A:D, 3, 0)The final 0 (or FALSE) locks to exact matches only. If nothing matches, you get #N/A — which is correct behaviour: no silent wrong answers.
Pro tip
Always use 0 for accounting work. Approximate match on financial data causes invisible errors.
Map values to ranges — tax brackets, commission tiers, discount bands.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, 1)Example
=VLOOKUP(B2, TaxBrackets!A:B, 2, 1)The final 1 (or TRUE) finds the largest value in column 1 that is ≤ your lookup value. The lookup column must be sorted ascending or results will be wrong.
Pro tip
Sort your bracket table A→Z before using this. One unsorted row breaks all results silently.
Pull data from another tab or workbook into your active sheet.
Syntax
=VLOOKUP(A2, SheetName!A:D, col_index_num, 0)Example
=VLOOKUP(A2, 'Jan Actuals'!A:C, 2, 0)Prefix the table range with the sheet name and an exclamation mark. Wrap in single quotes if the sheet name contains spaces. For external workbooks, include the full file path before the sheet name.
Pro tip
Close the source workbook after linking — open workbooks slow down recalculation on large files.
Two-step data retrieval — look up a code, then look up what that code means.
Syntax
=VLOOKUP(VLOOKUP(A2, table1, col, 0), table2, col, 0)Example
=VLOOKUP(VLOOKUP(A2, Employees!A:B, 2, 0), Departments!A:C, 3, 0)The inner VLOOKUP returns a value that becomes the lookup_value for the outer VLOOKUP. Here, row A2 holds an employee ID. The inner formula fetches their department code. The outer formula fetches the department name from the Departments table.
Pro tip
Debug inside-out: confirm the inner formula returns what you expect before wrapping it.
Replace #N/A errors with a fallback so your report stays clean.
Syntax
=IFERROR(VLOOKUP(lookup_value, table_array, col, 0), "fallback")Example
=IFERROR(VLOOKUP(A2, Products!A:C, 2, 0), "—")IFERROR wraps any formula. If the inner formula returns an error of any kind, the fallback value is used instead. Use "—" or "Not found" or 0 depending on whether the column feeds further calculations.
Pro tip
Avoid using 0 as a fallback in columns that sum — a "not found" zero will silently inflate totals.
At a glance
| Error | What it means | Fix |
|---|---|---|
#N/A | Lookup value not found in the first column | Check spelling, trailing spaces, or data type mismatch (text vs. number) |
#REF! | col_index_num exceeds the width of your table_array | Count the columns in your range — col_index_num must not exceed that count |
#VALUE! | col_index_num is 0 or negative, or text was passed where a number is required | Make sure col_index_num is a positive integer ≥ 1 |
Wrong value returned | Approximate match (1) is on when exact match (0) was intended | Add a 0 as the 4th argument to force exact match |
Slow recalculation | VLOOKUP across entire columns (A:D) on large datasets | Lock the range to real data rows: A2:D10000 instead of A:D |
Want to go deeper?
VLOOKUP Mastery for Accountants — video lesson, exercise file, and lifetime access for $29.