Match with Complex Lookup_array

[karlsven]

Hi,

I am trying to do something that I thought should be simple. I am using a
MATCH function to extract data from a different file than the one I am
working in. Now this works great as long as I am always referencing the exact
same file, BUT the problem arises when I need to able to change the lookup
file depending on which file I want to look up in.

I am basically populating a list of 10 columns and 40 rows where every
single row will collect data from a different file. I have made my formula
general so that when I have the file name in one column I can just pick up
the file name and insert it into my formula using an INDIRECT function like
this: NDIRECT("'["&$B3&".xls]Sheet1'!$H:$H") and then reuse the exact same
formula on the next row with a new file name. Now this works great AS LONG AS
the file that I am referencing is open. My reasoning is that the indirect
formula doesn’t just give the reference to the array, but it actually returns
the array values as well and therefore requires the file to be open.
Now I tried to replace the INDIRECT function with a TEXT function, but MATCH
doesn’t seem to like that even though I know that the resulting text is
correct.

Now we are getting into where my basic problem is. I want to use MATCH to
extract date from these other files and I used INDIRECT to solve my complex
reference problem in the MATCH formula, but that doesn’t allow me to extract
date from files that are not open. Is there any other formula that I may
substitute INDIRECT with to make my MATCH function work. My MATCH formula
looks like this: MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0)

Now I would have an exact identical MATCH nested into an INDEX function to
find the value that will actually be at the intersection of the results from
the two different MATCH functions. My total formula looks like this:
=INDEX(INDIRECT("'["&$B3&".xls]Sheet1'!$A:$iv");MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0);MATCH("Budget
"&I$2;INDIRECT("'["&$B3&".xls]Sheet1'!$2:$2");0))

Note that my version of Excel uses: ; to separate the different conditions
in the formulas and not , as that is the decimal indicator here in Norway.

Looking forward to any creative suggestions.

Thank you in advance,

 

[Peo Sjoblom]

You would need an add-in for this


http://xcell05.free.fr/morefunc/english/index.htm


has a function called indirect.ext which will work for closed source files



or


ftp://members.aol.com/hrlngrv/

look for pull.zip



to install add-ins


http://www.mvps.org/dmcritchie/excel/install.htm


the former has an installation file




--


Regards,


Peo Sjoblom

 

[karlsven]

Peo,

Thanks for the quick reply. I am somewhat hesitant against add-ins due to
the simple reason that I work in a larger corporation with a central IT
department that swipes our computers clean once in a while. I must say they
are quite good, we almost never have any downtime, but they do limit quite a
few things. Another reason is that I would like to be able to have other
people open the same file and being able to retract the same information.

Is there any other way that add-ins to solve this problem?

Thank you again in advance.

karlsven

You would need an add-in for this


http://xcell05.free.fr/morefunc/english/index.htm


has a function called indirect.ext which will work for closed source files



or


ftp://members.aol.com/hrlngrv/

look for pull.zip



to install add-ins


http://www.mvps.org/dmcritchie/excel/install.htm


the former has an installation file




--


Regards,


Peo Sjoblom

Hi,

I am trying to do something that I thought should be simple. I am using a
MATCH function to extract data from a different file than the one I am
working in. Now this works great as long as I am always referencing the
exact
same file, BUT the problem arises when I need to able to change the lookup
file depending on which file I want to look up in.

I am basically populating a list of 10 columns and 40 rows where every
single row will collect data from a different file. I have made my formula
general so that when I have the file name in one column I can just pick up
the file name and insert it into my formula using an INDIRECT function
like
this: NDIRECT("'["&$B3&".xls]Sheet1'!$H:$H") and then reuse the exact same
formula on the next row with a new file name. Now this works great AS LONG
AS
the file that I am referencing is open. My reasoning is that the indirect
formula doesn't just give the reference to the array, but it actually
returns
the array values as well and therefore requires the file to be open.
Now I tried to replace the INDIRECT function with a TEXT function, but
MATCH
doesn't seem to like that even though I know that the resulting text is
correct.

Now we are getting into where my basic problem is. I want to use MATCH to
extract date from these other files and I used INDIRECT to solve my
complex
reference problem in the MATCH formula, but that doesn't allow me to
extract
date from files that are not open. Is there any other formula that I may
substitute INDIRECT with to make my MATCH function work. My MATCH formula
looks like this: MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0)

Now I would have an exact identical MATCH nested into an INDEX function to
find the value that will actually be at the intersection of the results
from
the two different MATCH functions. My total formula looks like this:
=INDEX(INDIRECT("'["&$B3&".xls]Sheet1'!$A:$iv");MATCH("Total for Set
(USD):";INDIRECT("'["&$B3&".xls]Sheet1'!$H:$H");0);MATCH("Budget
"&I$2;INDIRECT("'["&$B3&".xls]Sheet1'!$2:$2");0))

Note that my version of Excel uses: ; to separate the different conditions
in the formulas and not , as that is the decimal indicator here in Norway.

Looking forward to any creative suggestions.

Thank you in advance,