Reports

It seems to be happening because PowerShell Export-Excel is creating a 'minimal' Excel file, with no version information. And when your EXCEL opens it it makes some assumptions, and thinking that XLOOKUP can potentially return an array, it makes sure that only the first item is returned (by adding the @). I am not sure how to 'inject' a version number (and make content compatible with that format) into the exported file. Since you do this row by row, why not use VLOOKUP?

    $data = Import-Excel -path "C:\SomeWorkbook.xlsx"
$row = 2
$data | ForEach-Object { 
    $_ | Add-Member -MemberType NoteProperty -Name "Role" -Value "=VLOOKUP(E$Row,UserData!A:F,3,FALSE)"
    $_ | Add-Member -MemberType NoteProperty -Name "Department" -Value "=VLOOKUP(E$Row,UserData!A:F,4,FALSE)"
    $_ | Add-Member -MemberType NoteProperty -Name "Division" -Value "=VLOOKUP(E$Row,UserData!A:F,5,FALSE)"
    $_ | Add-Member -MemberType NoteProperty -Name "Manager" -Value "=VLOOKUP(E$Row,UserData!A:F,6,FALSE)"
    $row++
}
$data | Export-Excel -path "C:\SomeWorkbook.xlsx" -worksheetname "Appdata"

79456339