Goal: Show Excel users how to write a macro alternative to Excel’s VLOOKUP or XLOOKUP functions

This article will show you how to write a macro alternative to the Excel functions of XLOOKUP and VLOOKUP. As you can see, we have a table of revenue data by company in Sheet1 – from Alpha down to Zulu.

In this example, we will randomly reference a handful of these company in Sheet2, which will be our output sheet. We will take Zulu, Tango, Alpha, X-Ray and Mike.

Most financial modelling and Excel users are aware of VLOOKUP, and to a lesser extent the new addition XLOOKUP, in order to reference data like this example. Instead of using these Excel lookup functions, we will write a macro employing variant arrays, which will execute more efficiently than applying indexed loops through the data set in Sheet1.

The macroLookup code

Go into Visual Basic for Applications and insert a Module. Always endeavor to notate your macro, as it can help at a later data with auditing the macro if necessary. Let’s call the macro, macroLookup

Disable these Excel properties before running the macro, as it will improve the macro’s performance. Now we need to declare the object variables for our lookup macro.

In some instances, when writing a macro, not specifying object variables will not prevent a macro from running successfully. However, they are an imperative with this macro because we are referencing variant arrays across two sheets.

Remember, when we write the below lines of code, it is vital to accurately reference and spell these object arrays precisely – otherwise the macro won’t correctly execute. Given we are looking up data from Sheet1, and referencing it into specific cells into Sheet2, it is critical to set the object variables for these two worksheets.

We will reference the index number of each sheet, as it will safeguard our code even if a user changes the name of the worksheet.

Next we need to locate the last row in our two columns by company name – the source column in column A of Sheet1 and the output column in column D of Sheet2. lastRowIn is the object variable, which will locate the last row in column A of Sheet1.

Let’s verify this by looking at Sheet1. We need to locate the last row based on the data in column A; the names of our companies from A2 to A27. Additionally, the lastRowOut object variable will need to locate the last row in Sheet2, which will be based on the company names list in column D.

There is an advantage to this approach to dynamically reference the last row of data. If we adjust our source data set in length in Sheet1, or want to reduce or expand the output list in Sheet2 of company names to lookup, the macro will be able to facilitate this change with our data.

Hence lastRowOut will be the object variable to remember the last row in column D for Sheet2 – our output sheet for this example.

Variant Arrays

The first variant array to account for is the source range of company names in column A in Sheet1. Note, the syntax Cells(1,1) is the cell address A1. Whilst the syntax Cells(lastRowIn, 2) will be the cell address of the last row of data in column B or 2.

To reiterate this, here is the output sheet – Sheet2. Whilst this is our input sheet – Sheet1.

Next, we need to write the code for the findArray variant. This variant array will record the output data in column D from Sheet2. The syntax Cells(1,4) is the cell address D1, Cells(lastRowOut,4) will reference the last row of data in column D or 4.

The output array will ensure we return the correct revenue value from the source table in Sheet1. The syntax Cells(1,5) is the cell address E1. Whilst the syntax Cells(lastRowOut,5) will denote the last row of data in column E or 5.

The macro loops

We will now add a simple error handler to our macro. As narrated here, we must now write a loop to cycle through the output names in column D of Sheet2.

Then a second loop to move through the input names from the source data in column A of Sheet1.

With both loops, the loop will stop once we hit the last row of data in each respective data range. It is now time to type the lookFor variant array. It’s value will be the value referenced in the findArray variant.

The findArray variant will cycle through the individual names in column D of Sheet2. Whilst if the company name inArray equals the company name found in lookFor, then our outArray value must equal the revenue value in inArray – column B of Sheet1.

Next j means we move down to the next company value in column A of Sheet1 – our input sheet. Next i denotes we will move to the next company value in column D in Sheet2 – our output sheet.

Finally this following code will ensure our lookup return will be the correct revenue number for our specific company name from column D in Sheet2

Clearly the output values need to be placed in column E of Sheet2 or by the column index number of 5.

Our macro is essentially complete. We can re-enable the following Excel properties, which were earlier disabled before we started writing the macro. Let’s review our macro before we try it out.

Running the lookup macro

We will insert a form control button & place our macro here. Let’s run the macro.

We will verify the outputs. Our macro is working. We will now add a few more companies in column D of Sheet2 – our output sheet. We will run our lookup macro once more – again it is working as required.

Here is the example workbook, which contains the XLOOKUP VLOOKUP macro.

Download Spreadsheet Vault SpreadsheetVault – VBA lookup (solution).xlsb