Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with Complex Lookup_array
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 doesnt 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 doesnt 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 doesnt 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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with Complex Lookup_array
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" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with Complex Lookup_array
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 "Peo Sjoblom" wrote: 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" wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Summing probably using Match at some point... | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Returning row # using match or index of repeated text in a complex table | Excel Worksheet Functions | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions |