#NAME Error in Excel 2010 but not in Excel 365 (related to INDIRECT or VLOOKUP function??)

Hi All-
I've built a workbook with a few different VLOOKUPs and named arrays in it. Everything works fine, regardless of machine, as long as its running Excel 365. However, when using Excel 2010, I'm getting a #NAME error.

Interestingly, I have two very similar formulas in different cells. One is getting the #NAME error, the other is not.

Rather than posting the entire workbook (which would require quite a bit of work to remove proprietary information), see the simplistic representation below:

CELL D5: "Select a color to show a list of fruit that match:"
CELL E5: is defaulted to "RED", but uses Data Validation to limit to Red, Yellow, Green

There are a few handler cells and rows in between, but CELL D15 is the problem cell. When you first open the document and the default RED selection is in E5, it correctly shows "Apple". However, if you change E5 to anything (including changing it back to "RED", it produces a #NAME error. Here's the formula:

=IF(VLOOKUP($D$8,INDIRECT($D$7),3,FALSE)=0,"ERROR",VLOOKUP($D$8,INDIRECT($D$7)3,FALSE))

The same selection in cell E5 also updates cell D17 with a slightly different formula. This formula works with no problems at all. It is:

=INDEX(INDEX(Table1,0,1),MATCH($E$5,INDEX(Table1,0,4),0))

Again, this all works fine in Excel 365, and the issues are in Excel 2010. I don't think that either VLOOKUP or INDIRECT are so new to Excel that they wouldn't work in 2010, but that's my best guess. Unfortunately, I need to distribute this tool to external customers and I've found that many of them don't have the latest version of Excel. Any help to get this working on older versions would be greatly appreciated!

Thanks Jeff - both for your prompt reply and for helping me realize that my attempt to "describe" the problem as opposed to posting an example was futile!

See attached workbook. When selecting a different "Internal Sales Person" the "Carrier Sales Rep" gives me a #NAME error, but the "Internal Support Team" does not.

The full version of the workbook has about 30 carriers (each of whom have their own Tab/ Named Range) to choose from and over 80 Internal Sales Reps. The "Internal
Support Team" displayed at the bottom is determined only by the "Internal Sales Person" selected. However, the "Carrier Sales Rep" displayed is determined by both
the "Internal Sales Person" as well as the "Carrier" selected. Thus, the end results of the two formulas are pretty similar, but the mechanisms needed to get there
are quite different.

Thanks. I was able to diagnose this in about 30 seconds using your file. It would have taken a lot longer to go back and forth in posts.

The formulas in row 15 are giving you a NAME error because they all reference D8, which is giving you a NAME error. I was wrong about D7 but D8 was my second suspect and has been found guilty. The formula in D8 is

Formula:

Please Login or Register  to view this content.

I am using Excel 2016. The _xlfn. indicates that the CONCAT function is not available in my version of Excel. It is not available in any version earlier than Office 365. CONCAT allows for full column and row references, such as CONCAT(A1:B5), where it will concatenate all cells in that range. CONCATENATE will not do that. But your formula doesn't need that. So use CONCATENATE instead to solve your problem.

Welp... there it is!! Thanks so much, Jeff. It figures that in as detailed a formula as the one I posted the problem would be in the "ENATE" of another cell!

There is a compatibility checker in excel for this purpose,

File > Info > Check for issues > Check compatibility.

This will tell you of any features that you have uses which are not compatible with older versions of excel. You need to do this in the version that the file works with, in your case, office 365.

Running the checker in excel 2010 will only highlight things that will work in 2010, but not in 2007 or older. It will not highlight things from 2013 or newer that are not compatible with 2010.