The below Index(Match VBA Formula only works when referencing MATCH($A4&$C5.. When the formula is used for each cell from H4:H & LastRow 7 it always reverts back to referencing cell $A4&$C4... I tried using FormulaArray except it doesn't with R1C1 and using R1C1 won't work either..
Can someone take a look and help? I need the formula to index(match formula to work for every row, not just the first. BTW, LastRow7 will always be different, i.e., H4:H100 OR H4:H2000...
'Here is the excel formula from the formula bar
Can someone take a look and help? I need the formula to index(match formula to work for every row, not just the first. BTW, LastRow7 will always be different, i.e., H4:H100 OR H4:H2000...
Code:
[COLOR=#333333]Range("H4:H" & LastRow7).FormulaArray = "=INDEX('[" & wcRef.Name & "]EVMAPL'!R4C8:R3000C8,MATCH(RC1&RC3,'[" & wcRef.Name & "]EVMAPL'!R4C1:R3000C1&'[" & wcRef.Name & "]EVMAPL'!R4C3:R3000C3,0))"[/COLOR]'Here is the excel formula from the formula bar
Code:
[/COLOR][COLOR=#333333]=INDEX('[BondPAS Recon 12-30-16.xlsx]EVMAPL'!$H$4:$H$3000,MATCH($A4&$C4,'[BondPAS Recon 12-30-16.xlsx]EVMAPL'!$A$4:$A$3000&'[BondPAS Recon 12-30-16.xlsx]EVMAPL'!$C$4:$C$3000,0))[/COLOR][COLOR=#333333]