Index-Match using variables

[Bill_Kro]

I am trying to insert an Index-Match function into a table.
I get an error message when I try to use string variables in the formula.

The below works:

Dim T1 As String
Dim T2 As String
T1 = "Table1"
T2 = "Table2"

Sheets("Orig").ListObjects(T1).ListColumns("Revised").DataBodyRange = _
"=INDEX(Table2[CheckVal],MATCH(Table1[@Key],Table2[Key],0))"

I got the "=INDEX...." using the macro recorder.

However if I try to replace portions like "Table2" with the variable "T2" it doesn't work.

I wrote the following:
Sheets("Orig").ListObjects(T1).ListColumns("Revised").DataBodyRange = _
"=INDEX(""&T2&""[CheckVal],MATCH(Table1[@Key],Table2[Key],0))"

My ultimate goal is to replace all the parts of the Index-Match function with variables.

Please let me know if you need more information (1st time posting)

 

[Peter_SSs]

Welcome to the MrExcel board!

T2 already is a string so just concatenate it with the rest of the formula string. Try

Sheets("Orig").ListObjects(T1).ListColumns("Revised").DataBodyRange = _
"=INDEX(" & T2 & "[CheckVal],MATCH(Table1[@Key],Table2[Key],0))"

 

[Bill_Kro]

Thank you Peter. That solved my problem. I just had the concatenate wrong. Thank you for the correction!

 

[Peter_SSs]

Thank you Peter. That solved my problem. I just had the concatenate wrong. Thank you for the correction!

You are welcome.