VBA Index Match with multiple criteria question

[Terranova1340]

Hi all hoping someone on here can give me a slap to the head and tell me what I am doing wrong.

I am trying to write and Index Match formula and I am running into an issue where I cannot pass the formula variables IE Sheet names

Range("D20").FormulaArray = "=INDEX(" & month_Sht_Name & "!B23:B40,MATCH('Month by Individual Analyst'!D16&'Month by Individual Analyst'!D$17,Month!$A$23:$A$40&'Month by Individual Analyst'!D$17,0))"

The code above works IF I replace the following code with the actual sheet name

" & month_Sht_Name & "

So when written like this: It works fine

Range("D20").FormulaArray = "=INDEX(Month!B23:B40,MATCH('Month by Individual Analyst'!D16&'Month by Individual Analyst'!D$17,Month!$A$23:$A$40&'Month by Individual Analyst'!D$17,0))"

I have spent quite a few hours playing with it and for the life of me I cannot figure out what I am doing wrong. Though I am sure it is something silly.


Any help would be greatly appreciated.

 

[iliace]

Interesting issue. I attempted to recreate your workbook, then tried this code - and it works fine:

Public Sub sV()
  Dim s As String
  Dim month_Sht_Name As String

  month_Sht_Name = "Month"
  
  s = "=INDEX(" & month_Sht_Name & "!B23:B40,MATCH('Month by Individual Analyst'!D16&'Month by Individual Analyst'!D$17,Month!$A$23:$A$40&'Month by Individual Analyst'!D$17,0))"
  
  ActiveCell.FormulaArray = s
  
  Debug.Print s
End Sub

So it is probably a broader issue with the way your workbook is set up. Can you give some more context? Most helpful would be the specific "issue" you're "running into", maybe a sample of what D20 is (or what sheet it is on), what is on month_Sht_Name (and data type, and what is its value), what are the values of 'Month by Individual Analyst'...... and so on.