Index and Match does not calculate, returns #VALUE!

[geoffmx]

I have two workbooks, current_month.xlsx and previous_month.xlsx

I would like to take the values in column J from previous_month.xlsx and enter them in column J of current_month.xlsx.

My script first imports the 'old' worksheet from previous_month.xlsx into current_month.xlsx.

Now that we have just current_month.xlsx to work with, Index and Match is used in the 'new' sheet to compare against values in three columns - Plugin_Name, Type, and Year_Month from the 'old' sheet.

The formula =INDEX(old!D:D,MATCH(1,(old!C:C=[@[Plugin_Name]])*(old!B:B=[@Type])*(old!A:A=[@[Year_Month]]),0)) works directly in Excel.

However, if I attempt it via the module, it does not calculate and instead returns #VALUE!.

Please advise!

current_month.xlsx
index_match_ps_script.txt
previous_month.xlsx

[dfinke]

after running the script, and you view the xlsx, what does the formula look like?

[geoffmx]

With Set-ExcelRange, the xlsx does not show the formula. The cells just have #VALUE! #.

If I run a for loop with
$open_e2.new.Cells["D$i"].Formula = "=INDEX(old!D:D,MATCH(1,(old!C:C=[@[Plugin_Name]])*(old!B:B=[@Type])*(old!A:A=[@[Year_Month]]),0))" and then call $open_e2.new.Cells["D$i"].Formula to see how the formula looks like in the script, it is as expected.

But the xlsx does not take the formula.

If I use $open_e2.new.Cells["D$i"].Value then the xlsx shows the formula.

[dfinke]

What happens if you use a simple formula?

[geoffmx]

A simple TRIM works.

$e2 = '.\current_month.xlsx'
$open_e2 = Open-ExcelPackage $e2 
$rows = $open_e2.new.Dimension.Rows

for ($i=2; $i -le $rows; $i++) {
   $open_e2.new.Cells["D$i"].Formula = "=TRIM(C$i)"
   $open_e2.new.Cells["D$i"].Formula # This is just to see the resulting formula in PS
}

Close-ExcelPackage $open_e2 -Calculate

A simple CONCAT does not work.

$e2 = '.\current_month.xlsx'
$open_e2 = Open-ExcelPackage $e2 
$rows = $open_e2.new.Dimension.Rows

for ($i=2; $i -le $rows; $i++) {
   $open_e2.new.Cells["D$i"].Formula = "=CONCAT(B$i,C$i)"
   $open_e2.new.Cells["D$i"].Formula # This is just to see the resulting formula in PS
}

Close-ExcelPackage $open_e2 -Calculate

The xlsx shows #NAME? and the formula shows up as =@CONCAT(B2,C2) with the implicit intersection operator @.
This is on MS Excel for Office 365, btw.

The @ has to be manually removed for the formula to work.
OR
use Control + Shift + Enter in Excel to enclose the formula in curly braces {=@CONCAT(B2,C2)}

I see that Set-ExcelRange has an -ArrayFormula parameter, but I'm not sure how to use it effectively, or if this would be a solution.

[geoffmx]

Any thoughts on this?

[dfinke]

No thoughts at the moment