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:

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 🙂