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:
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:
- Use a helper formula to concatenate all values in both tables: (i.e. =K2&L2&M2&N2&O2 )
- 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)
- 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
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 🙂
How about Microsoft make the stand alone version of Office 2016 match the Office 365 version of Excel!
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.
BITRSHIFT is another function from older versions of excel. It’s available in LibreOffice Calc.
Edit: should be “missing from older versions”
Huh you can also just use replace _xlfn.CONCAT to CONCATENATE
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.