Fix an Excel VBA VLOOKUP Formula with a Dynamic MATCH Column
Question details
Create a valid VBA formula string that uses VLOOKUP and MATCH to select a lookup column dynamically.

- 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.
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.
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.
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.
In Excel, open Developer > Visual Basic, then choose Insert > Module.
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
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.

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.

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.




