VBA & Macro Problems

Fix an Excel VBA VLOOKUP Formula with a Dynamic MATCH Column

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Create a valid VBA formula string that uses VLOOKUP and MATCH to select a lookup column dynamically.

Fix an Excel VBA VLOOKUP Formula with a Dynamic MATCH Column
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro inserts a VLOOKUP formula whose return column is determined by a matching header.
Observed behavior
Nested quotation marks and dynamic column references can cause errors when constructing the formula string.
Before you start

Identify the lookup table, its header row, and the destination cells. The lookup value must be in the table's first column, and the header range must cover the same columns in the same order as the table.

Solution 1Recommended

Build the VLOOKUP formula with MATCH and COLUMN

Use this approach when the destination column's header identifies which table column VLOOKUP should return.

MATCH returns the position of the destination header in the lookup table's header range. VLOOKUP uses that position directly, so you do not need to convert it to a column letter.

The example uses values in Report column A, destination headers in Report row 1, and a lookup table on the Data sheet. Adjust the sheet names and ranges to fit your workbook.

1
Prepare the headers

On the Report sheet, enter lookup values in A2:A100 and a header in B1 that exactly matches a header in Data!A1:G1. Place the lookup table data in Data!A2:G100.

2
Open the VBA editor

In Excel, open Developer > Visual Basic, then choose Insert > Module.

3
Add the formula code

Paste this macro into the module: Sub FillLookupFormula() Dim lookupTable As Range Dim lookupHeaders As Range Set lookupTable = ThisWorkbook.Worksheets("Data").Range("A2:G100") Set lookupHeaders = ThisWorkbook.Worksheets("Data").Range("A1:G1") ThisWorkbook.Worksheets("Report").Range("B2:B100").Formula = _ "=VLOOKUP($A2," & lookupTable.Address(External:=True) & _ ",MATCH(INDEX($1:$1,COLUMN())," & _ lookupHeaders.Address(External:=True) & ",0),FALSE)" End Sub

4
Run and check the macro

Run FillLookupFormula from the VBA editor. Select Report!B2 and check the formula bar; then verify that the returned value matches the lookup value and header.

Build the VLOOKUP formula with MATCH and COLUMN
Check unmatched values: MATCH returns #N/A if the destination header is absent from the header range. VLOOKUP also returns #N/A if it cannot find the lookup value in the table's first column.
Free Microsoft Office alternative

Work with Lookup Formulas in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move and work with spreadsheets. Excel VBA macro compatibility can vary, so test any macros separately.

Use VLOOKUP and MATCH formulas to find values by a matching column headerOpen and edit common Microsoft Excel formats, including XLSX and XLSNavigate a familiar spreadsheet interface when moving from ExcelUse a free, lightweight office suite for everyday documents
microsoft office alternative - wps office

Frequently Asked Questions

How do I include quotation marks inside a VBA formula string?

Double each quotation mark that belongs to the worksheet formula. For example, to insert =IF(A2="","",A2), assign Range("B2").Formula = "=IF(A2="""","""",A2)" in the VBA editor.

Why does MATCH return #N/A in the generated formula?

Check the destination header in Report row 1 against Data!A1:G1. With MATCH's third argument set to 0, the header must match an entry in that range.

Why must the header range align with the VLOOKUP table?

MATCH returns a position within the header range, and VLOOKUP treats that position as a column number within its table. Both ranges must start at the same table column and cover the columns in the same order.

How can I inspect the formula VBA inserted?

After running the macro, select a destination cell such as Report!B2 and inspect the formula bar. Check the sheet references, lookup cell, header range, and quotation marks.