Excel changes a formula created in Google sheets

[yashgt]

I have created a Table in google sheets with a formula:

=IF($D4=0, $E4 & "-" & INDEX( $D4:$H$5005, IFNA(MATCH(0,$D5:$D$5005,0),MATCH(TRUE,$D5:$D$5005="",0)), 2),"").

When I download the sheet as a .xlsx file, the formula changes to:

=IF($D4=0, $E4 & "-" &@ INDEX( $D4:$H$5005,@ IFNA(MATCH(0,$D5:$D$5005,0),MATCH(TRUE,@$D5:$D$5005="",0)), 2),"").

Excel is not able to recognize the @ symbol and shows a #NAME error for the cell.
How to prevent the change in formula?

 

[Alex Blakenburg]

Firstly does that even work in Google sheets, typically for an index match formula your initial range and match ranges have to line up, your formulas have the initial range from 4 : 5005 but the match from 5 : 5005.

Secondly is it a real table, as in a Table Object ?
A likely issue is that you are using a Dynamic array formula in Google sheets and you would need Excel 2021 or MS 365 for that to automatically convert to Excel.

 

[RoryA]

Does it help if you use:

=ARRAYFORMULA(IF($D4=0, $E4 & "-" & INDEX( $D4:$H$5005, IFNA(MATCH(0,$D5:$D$5005,0),MATCH(TRUE,$D5:$D$5005="",0)), 2),""))

in Google Sheets? (though as Alex said, your ranges are mismatched so you'll never get a match returning E5005.

 

[yashgt]

Firstly does that even work in Google sheets, typically for an index match formula your initial range and match ranges have to line up, your formulas have the initial range from 4 : 5005 but the match from 5 : 5005.

Secondly is it a real table, as in a Table Object ?
A likely issue is that you are using a Dynamic array formula in Google sheets and you would need Excel 2021 or MS 365 for that to automatically convert to Excel.

The formula works fine in Google sheets. The mismatch is deliberate.
In excel, if I remove the @ it works too. But I would like to make sure I don't have to remove the @ every time I download the gsheet.

 

[Alex Blakenburg]

I think it is behaving differently in MS365 than it is for you in O2013.
In MS365 when I download either as is or using Rory's ArrayFormula, I get the Google sheets formula surrounded by parenthesis { } indicating its an array formula and it shows up as @Name?.
If I copy in your resulting formula with the @ in it then running the below in the immediate window fixes it for me but I am not sure it will work in your version but you can give it a try.

ActiveSheet.UsedRange.Formula2 = Application.Substitute(ActiveSheet.UsedRange.Formula2, "@", "")

If it doesn't work try this one in the immediate window:

ActiveSheet.UsedRange.FormulaArray = Application.Substitute(ActiveSheet.UsedRange.Formula, "@", "")