1

When I export/download a Google Sheet containing cells with the IFNA() formula to Excel, I get #name? errors in those cells when opening the file in Excel (Excel for Mac version 16.32). Strangely enough, when I just enter the cell in Excel to edit and press return (without any changes made or visible), the error disappears and the formule works as expected.

I've tried copy&pasting the formula into the same cell in Excel and several other attempts to somehow force re-evaluation or recalculation but that doesn't solve the problem. Also strangely enough, when opening the file in Libreoffice some cells don't show this error and other do with no visible difference why.

I did notice that the IFNA() function in Excel has 2 mandatory arguments, while in Google Sheet the second argument is optional. As I have 2 arguments everywhere, it should not be a problem.

Just for reference, the formule is as follows:

=IFNA(-VLOOKUP($B5;Othersheet!$A:$N;MONTH(C$2)+1;0);0)

Where $B5 contains a text value that is used in the VLOOKUP formula and C$S a date that is used to determine the column number for the VLOOKUP value, looking up a value in a matrix on another sheet. The VLOOKUP formule should not be the problem as it works perfectly when re-entering the same formule without any changes directly in the cell in Excel.

Improve this question
4
  • any reason not to use IFERROR instead of IFNA ? Commented Dec 17, 2019 at 18:50
  • No, just that IFNA() fitted my purpose and IFERROR seemed a little more complicated (I like simple solutions :-) but I'm willing to give it a go. Is IFERROR more compatible with Excel and is not suffering from the described problem? Commented Dec 19, 2019 at 9:15
  • should be more stable Commented Dec 19, 2019 at 13:58
  • I tried it and it indeed doesn't show the behaviour of IFNA when exporting so it works! Thanks for the suggestion. I actually mixed up IFERROR with an older function in Excel that used to have more arguments. The only disadvantage of using IFERROR might be that it catches all errors instead of only #N/A but I find a error free export to Excel more important. Commented Dec 19, 2019 at 22:04

1 Answer 1

Reset to default
1

try:

=IFERROR(-VLOOKUP($B5; Othersheet!$A:$N; MONTH(C$2)+1; 0); 0)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.