I SEE “XLFN” !?!? WHAT IS HAPPENING?

Despite how amazing Excel is there are times when you’ll find yourself shaking with fear! For example, have you ever seen anything as scary as this  =ISERROR(FIND(_xlfn.CONCAT($A2:$E2),_xlfn.CONCAT($I$2:$M$6))) What is xlfn ???

 

What’s The Story?

You open your friend’s Excel file and discover that some of the formulas aren’t working and include the letters XLFN ! You call your friend but he says that everything looks perfectly fine on his laptop. Did you get too much sun? Did you eat some bad fish? Did you smoke one of Oz du Soleil’s cigars?

 

What Is XLFN?

xlfn is a prefix added to functions that don’t exist in the version of Excel that you are using

You have Excel 2013 and your friend has a newer version of Excel (Office 365 or Excel 2016). He uses a cool new function and sends the file to you. As you have Excel 2013 this cool new function doesn’t yet exist and you see xlfn in front of the function.

 

Recent XLFN Example

Last year I discovered David Hager’s Excel blog. He shares a lot of neat ideas. I recently saw this post: Conditional Format Rows in List 1 that are Not in List 2

When I opened the file I noticed that the solution wasn’t working for me. I went back to David’s post and looked at this pic:

TableAntiCompareCF1

I noticed that David used the CONCAT function. This must be a new Excel 2016 (or Office365?) function! There are a bunch of really interesting new functions but I’m still using Excel 2013.

 

What Does Microsoft Recommend?

“Remove the unsupported functions, or if possible, replace the unsupported functions with supported functions.” Also, read this.

 

OK. So, Is There A Workaround?

Although not as easy as David’s solution we can still produce the same end result.

Here are the steps to my workaround solution:

  1. Use a helper formula to concatenate all values in both tables: (i.e.  =K2&L2&M2&N2&O2  )
  2. Adding this =ISNUMBER(MATCH(A2,$J$2:$J$6,0)) shows whether or not the row is found (Cell A2 is key in table 1. Column J is key in table 2) 
  3. Add this formula inside your conditional formatting rule:  =$B2=FALSE

 

Excel 2013 Workaround Solution

Here is David’s Excel file that includes my workaround solution for those that don’t have Excel 2016.

 

Learn From Excel MVP David Hager

You can find David at https://dhexcel1.wordpress.com/ and https://twitter.com/dhExcel

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

There are so many amazing things that you can do with Excel.

Check out my recommended Excel Training section.

Check out my videos and my blog posts.

Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂

6 Comments I SEE “XLFN” !?!? WHAT IS HAPPENING?

  1. Juan Manuel Lopez Felizzola

    This can also happen when you have teammates working in a different language Excel than you. the xlfn, acts just like a flag that your local Excel creates to indicate that it tried looking for that formula but didn’t find it.
    I don’t really know who on Ms decided that formulas in Excel should be translated and not ported between languages when you open the workbook, most surely in an effort to increase end-user retention of the formulae.

    Reply
  2. Steve

    MS is famous with every new version of Excel to break VBA code (older version of addins don’t work any more producing hidden module errors that u cant fix) and this one is new to me – _xfln. flag on formulas that worked on previous version of Excel now on new version doesn’t. Also formulas break when u UPGRADE Excel.
    Seems to me that i need spreadsheet in which i can code without a fear of code break and constant code changes. When formulas are complex, complicated and dependent then in such cases u have to re-code everything. Excel isn’t reliable solution.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *