VLOOKUP Formula in Excel VBA

There are two distinct ways to use VLOOKUP from VBA: writing the VLOOKUP formula as text into a cell using code, so it behaves exactly like a manually typed formula, or calling Application.WorksheetFunction.VLookup directly to get a result back into a VBA variable without touching a cell at all.

This page covers both approaches using a small employee table, since which one to use depends on whether the result needs to live in a cell or just inside the macro's logic.

This code inserts a normal VLOOKUP formula into cell F2, the same as typing it manually:

This approach skips the worksheet cell entirely and stores the result in a VBA variable, useful when the lookup result feeds into further macro logic rather than being displayed directly:

WorksheetFunction.VLookup raises a runtime error if no match is found, rather than returning #N/A silently, so it needs to be wrapped in error handling:

For applying the same lookup down an entire column programmatically, a loop combined with the WorksheetFunction approach avoids inserting hundreds of individual cell formulas:

For anyone who reached for VBA mainly to avoid manually copying a formula down hundreds of rows, Turbo Excel VLookup achieves the same end result - matching an entire dataset in one action - without needing any macro code written or maintained.

A worksheet with thousands of VLOOKUP formulas recalculating against a large table_array can slow Excel down noticeably, especially if the range isn't locked and Excel has to re-evaluate a shifting reference on every keystroke.

Converting the source range into an Excel Table (Insert > Table) before referencing it in VLOOKUP tends to help, since Table references automatically resize with the data and stay consistent without needing manual $ locking.

Before applying a new VLOOKUP formula to an entire column, it's worth testing it on a single row and manually verifying the result against the source data, since a mistake caught in one cell takes seconds to fix, while the same mistake copied down five hundred rows takes much longer to untangle.

This is especially worth doing whenever the source table_array has changed shape recently, since a resized or reordered table is the single most common reason a previously reliable formula starts returning unexpected results.