Knowing how to perform a lookup task is important and critical in Excel. Majority of the spreadsheets out there probably have some sort of lookup formula embedded.

Perform a Vlookup task is pretty easy on the Excel UI, you simply use the VLOOKUP (or INDEX/MATCH) function and that’s it.

In VBA, there is no generic lookup function, so most people who write VBA simply create a function data type variable and to call the VLOOKUP function. Although that method works, but imaging if you have multiple lookup values with same text but different corresponding values, then VLOOKUP will only extract the first found instance.

In this tutorial I will show you how I usually perform a lookup task in VBA, and with my method, we can extract all the corresponding values.

VBA Script (FindNext Method)
VBA Script (FindPrevious Method)