Indirectly Referencing Table Headers in VBA

Hey All,

I have a formula in my worksheet that uses InDirect to "slide" along the table headers to get to the correct column to Index/Match, this is the formula...


=IF(Category2Dropdown="","#N/A",INDEX(Category2Table[F1],MATCH(Category2Dropdown,INDIRECT("Category2Table["&Category1Dropdown&"]"),0)))

This is all a part of 3 'dropdowns' that dependent on each other, I got the first to work in VBA, but it doesn't "slide" across to the appropriate header. For 'dropdown 2 & 3' I can't get the indirect portion to work. Here is the code I used to get the Category 1 dropdown to work...


Dim Cat1 As Variant

Cat1 = Application.Index(Range("Category1Table[Column1]"), _
Application.Match(ComboBox1.Value, Range( _
"Category1Table[Category1]"), 0), 1)

I've attached the worksheet to hopefully give you a better idea of what I'm struggling with. The important part is at the bottom of the code in the userform, and the example of how it works without VBA is in cells AG2:AI2, which are referencing the sheet 'Imported Product Groups'.

Any advice or solutions you guys could offer would be greatly appreciated, this has already taken up my entire morning

Thank you