- BY Kevin Lehrbass
- POSTED IN Errors
- WITH 4 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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**

**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 🙂

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”