INDIRECT function not working inside MATCH with dynamic ranges

**fotografer** · 03-25-2014, 11:09 PM

Hi,

Long time lurker in here and have found very useful information to incorporate into my Excel worksheets. Thanks in advance to all the Excel gurus here.

I have perplexing problem. I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.

I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.

Dynamic Ranges INDIRECT v2.xlsb

Much thanks in advance.

**JBeaucaire** · 03-26-2014, 12:13 AM

There is no reason you can't use a dynamic named range in a MATCH function. Your formulas in J7, J9 and J11 fail because XX is not in those lists.

As for your overall assertion, you are correct in that cannot use a Dynamic Named range in an INDIRECT formula. There are ways around that, meaning NOT using INDIRECT().

**fotografer** · 03-26-2014, 01:58 AM

Originally Posted by JBeaucaire

There is no reason you can't use a dynamic named range in a MATCH function. Your formulas in J7, J9 and J11 fail because XX is not in those lists.

As for your overall assertion, you are correct in that cannot use a Dynamic Named range in an INDIRECT formula. There are ways around that, meaning NOT using INDIRECT().

Thanks for the quick reply. Cells J7, J9, J11 and J13 were meant to serve a checking rows against the actual formula in J5.

Anyway, after some mucking around, I managed to "solve" the issue by using choose/match functions as per the attached file.

Dynamic Ranges INDIRECT v3.xlsb