Index Match Help

[ItalianPlatinum]

Hello - i am not very versed with index match but I am trying to put it to use because I need to lookup multiple conditions. I was able to put a formula together, then using VBA to apply the formula. But when I apply the formula it is putting @ signs in front of my ranges when it does that it causes #VALUE errors. If I remove them manually in the cell my formula operates. Do I have a problem with my formula or my VBA or both?

Formula: =INDEX('Trend'!$C$11:$C$5000,MATCH(0,('Trend'!$A$11:$A$5000=$A10)*('Trend'!$B$11:$B$5000=$D10),0))
Desired result:

5/29/2023

.Range("I8:Q" & lastrow).Formula = "=IF($R8<>"""",INDEX('Trend'!C$11:C$5000,MATCH(0,('Trend'!$A$11:$A$5000=$A8)*('Trend'!$B$11:$B$5000=$D8),0)),VLOOKUP($D8,'Trend'!$B$11:$P$5000,COLUMNS($I$8:J$5000),0))"

Formula after VBA: =IF($R10<>"",INDEX('Trend'!C$11:C$5000,MATCH(0,(@'Trend'!$A$11:$A$5000=$A10)*(@'Trend'!$B$11:$B$5000=$D10),0)),VLOOKUP($D10,'Trend'!$B$11:$P$5000,COLUMNS($I$8:J$5000),0))

#VALUE!

 

[Anfinsen]

Hello,
if you turn off structured references, this should fix your issue.
In excel, go to file, click options, select formulas, uncheck the box labeled "Use table names in Formulas".

Cheers!

 

[ItalianPlatinum]

that didnt do anything

 

[Anfinsen]

that didnt do anything

in your VBA code, instead of
.Formula
try using
.FormulaR1C1

 

[ItalianPlatinum]

I get this error

 

[Peter_SSs]

Instead of .Formula use .Formula2

 

[ItalianPlatinum]

That looks to be working, having an issue with my formula itself but that is a separate issue. So Formula2 looks to be it. I cannot say I ever heard of that before is that new? Should I be using that for my formula array?

 

[Peter_SSs]

is that new?

Yes, since dynamic arrays were introduced.

Should I be using that for my formula array?

I don't know what your formula array is but see below for more information (plus you can always try each way & see )
Range.Formula2 property (Excel)
Cell Formulas