VLOOKUP Automation Mastery

Open a new workbook. In Sheet1, create a reference table with columns: Account Code (A), Account Name (B), Normal Balance (C). Populate it with 10 representative chart-of-accounts rows.

In Sheet2 (your working trial balance), enter an Account Code in cell A2. In B2, type: =VLOOKUP(A2,Sheet1!$A:$C,2,FALSE) — the FALSE argument forces an exact match, which is always correct for account codes.

Extend the formula down for all rows using Ctrl+D. Watch how the account name pulls automatically from your reference table with zero manual entry.

Handle #N/A errors cleanly: wrap the formula in IFERROR — =IFERROR(VLOOKUP(A2,Sheet1!$A:$C,2,FALSE),"") — so missing codes show blank instead of an error flag.

Use absolute references ($A:$C) on the lookup range so the formula survives being copied across columns without breaking the reference.

Before recording, organize your workbook: reference table in Sheet1, working data in Sheet2. Name your sheets clearly — 'Reference' and 'TrialBalance'.

Go to Developer → Record Macro. Name it RunVLOOKUP. Assign Ctrl+Shift+V as the shortcut. Set 'Store macro in' to 'This Workbook' so it travels with the file.

While recording: click cell B2 on TrialBalance, type the IFERROR-wrapped VLOOKUP formula, press Enter, then select B2 again and use Ctrl+Shift+End to select to the last row. Press Ctrl+D to fill down. Stop recording.

Test: delete column B, press Ctrl+Shift+V. The macro re-populates the entire column in under a second.

Open the VBA editor (Alt+F11) to inspect the recorded code. You'll see a Cells.AutoFill line — that is the fill-down step. Understanding this line lets you modify the range dynamically later.

Upgrade the macro to handle a variable number of rows. Replace the fixed range with: lastRow = Cells(Rows.Count, "A").End(xlUp).Row — this finds the last populated row in column A automatically.

Update the AutoFill call to: Range("B2:B" & lastRow) so the macro scales to any client file regardless of size.

Add a second VLOOKUP column to pull Normal Balance: in column C, use column index 3 in the lookup. Extend the macro to fill both B and C in one keystroke.

Save the workbook as a macro-enabled template (.xltm). When you open it for a new engagement, the macro is ready — just paste the client's account codes and run.

Pro tip: protect the Reference sheet (Review → Protect Sheet, allow only row selection) so junior staff can't accidentally overwrite the lookup table.