Jan 06 2022 11:41 AM - edited Jan 06 2022 11:54 AM
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! :):)
Jan 06 2022 12:57 PM
That means function which prefixed with _xlfn are not available for the current version of Excel. The only fix is to use proper version.
Jan 06 2022 05:08 PM
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.