Excel formulas change when uploaded or shared

Copper Contributor

Hello! First of all thank you for your attention :)

 

I am trying to share a .xlsx worksheet through e-mail or my drive. However, whenever I try to open it in any other computer, instead of the formulas working, they show the "#NAME?" error. When I inspect the error, I realize that most of my formulas have different characters added at the start of the function. As an example I have the following formula: 

 

=IF(C7="";"";XLOOKUP(C7;'ZIP Codes DB'!B4:B67;'ZIP Codes DB'!C4:C67))

 

After sending the .xlsx through gmail, the cell shows the #NAME? error and the formula bar looks as following:

 

{=IF(C7="";"";XLOOKUP(C7;'ZIP Codes DB'!B4:B67;'ZIP Codes DB'!C4:C67))}

 

Which means, in the process of sending it, the program added the { } symbols. 

 

another example would be:

 

=IF(C2="";"";INDEX(Sheet4!E4:BB33;XMATCH(Sheet1!H8;Sheet4!A4:A33);XMATCH(C2;Sheet4!E3:BB3))

 

after sharing the error looks as following:

 

=IF(C2="";"";INDEX(Sheet4!E4:BB33;_xlfn.XMATCH(Sheet1!H8;Sheet4!A4:A33);_xlfn.XMATCH(C2;Sheet4!E3:BB3))

 

note the _xlfn. addition in the formula. 

 

Does anyone know how to fix this error and why is this happening?

 

Thanks a lot in advance! :):)

2 Replies

@bcampos507 

That means function which prefixed with _xlfn are not available for the current version of Excel. The only fix is to use proper version.

@bcampos507 

 

IMHO, the simple "fix" is:  do not use the new functions when the old functions would do just as well.

 

Change

=IF(C7="";"";XLOOKUP(C7;'ZIP Codes DB'!B4:B67;'ZIP Codes DB'!C4:C67))

 

to

=IF(C7="";"";VLOOKUP(C7;'ZIP Codes DB'!B4:C67, 2, 0))

 

Change

=IF(C2="";"";INDEX(Sheet4!E4:BB33;XMATCH(Sheet1!H8;Sheet4!A4:A33);XMATCH(C2;Sheet4!E3:BB3))

 

to

=IF(C2="";"";INDEX(Sheet4!E4:BB33;MATCH(Sheet1!H8;Sheet4!A4:A33,0);MATCH(C2;Sheet4!E3:BB3,0))

 

Aside:  I would use absolute range references.  It makes it easier to copy formulas.  But that applies to the X-functions, as well.