Why is Excel not able to compute MaxIfs function? (_xlfn. error)


I'm using Microsoft Office Excel 2016 and have the following formula in 2 adjacent columns.

=VLOOKUP(MAXIFS('USD Account'!$B$6:$B$1048576,'USD Account'!$B$6:$B$1048576,"<=29/02/2016"),'USD Account'!$B$6:$H$1048576,7,TRUE)

However, no matter how many times I tried re-opening the file, it always shows as below:

=VLOOKUP(_xlfn.MAXIFS('USD Account'!$B$6:$B$1048576,'USD Account'!$B$6:$B$1048576,"<=29/02/2016"),'USD Account'!$B$6:$H$1048576,7,TRUE)

Why is "_xlfn." being prefixed to the formula? Even when I try deleting the "_xlfn." manually, it comes back.

I have tried re-installing Office 16, and also installed Office 13 as a last ditch attempt, but, even Excel 2013 gave me the same problem. 

Why is this happening? When I open the file via OneDrive, the Online Excel app reads my file perfectly and delivers the formula result without any problem. Can someone please help? Thanks for reading!


Hi, VelvetAssassin,

I can understand how that could be frustrating, but I'm glad you found a solution using INDEX and MATCH.

Access to new features is typically provided first to users with O365 accounts, then to users with perpetual licenses in later versions. From your comments it looks like you have a perpetual license, not O365, so MAXIFS shouldn’t have been offered yet.

In some uncommon cases, initial installation may take some time to validate your license (up to 5 days) and you may see new features in that period.  While this helps users get up and running faster, it can be confusing at times.  We're investigating possible ways to make the initial experience more predictable in the future.



Microsoft Excel Team

5 people found this reply helpful


Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated March 29, 2021 Views 13,683 Applies to: