Thread: Variables in Match

Since this spans over multiple files it would be hard to upload an attachment so bear with me as I try to explain it.

I made a formula that would take the left 4 digits of cell A4 and search for them in the column of another workbook. The formula also takes the right 2 digits and searches for them in a row in the other workbook. Using the DIRECT and ADDRESS functions, I use the values returned by the match (offset by 10 and 3 because the ranges don't start at 1) to get the value where they intersect on the other workbook. This formula works fine:

=INDIRECT("'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!"&ADDRESS(MATCH(LEFT(A4,4),'[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),'[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$D$5:$K$5)+3))

Then I thought that it would be good to use variables, because the location and name of the other file might change. I created a sheet called "Variables" and I put the name of the other file in B2, the directory in B3, and the sheet name of the other file in B4. I used INDIRECT.EXT to do the following, which also works just fine:

=INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]"&Variables!B4&"'!"&ADDRESS(MATCH(LEFT(A4,4),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$D$5:$K$5)+3))

But as you can see, the MATCH function still contains specific information. I can't figure out a way to make it refer to a variable path like what I did with the first part of the formula. Is it possible for MATCH to look in a variable location? In other words, I want it to look in a range in file "N", where "N" is the filename specified by another cell (in this case Variable!B2).

I hope that makes sense.

Thanks

Looks like you have woven yourself a complicated web, but can't you just do this:


=INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]"&Variables!B4&"'!"&ADDRESS(MATCH(LEFT(A4,4),'C:\Documents and Settings\Desktop\Budget\[Financial Plan 2009-2013 (b) Dec 31 - Final.xls]Facilities'!$B$11:$B$57)+10,MATCH(RIGHT(A4,2),INDIRECT.EXT("'"&Variables!B3&"["&Variables!B2&".xls]Facilities'!$D$5:$K$5"))+3))

I didn't set up your whole situation so I can't guarantee this works. I also don't know how INDIRECT.EXT works. Is it an add-in?

Jazzer, Indirect.Ext is included in morefunc.xll

It can perform the Indirect on closed workbooks.