I am using the INDIRECT function to get data from a file and would like to use the INDEX,MATCH function instead. This would mean the i can access the data witout opening the file. As i have many files that i get data from i get the filename suffix from a cell. this works for INDIRECT but i can get it to work for INDEX.

=VLOOKUP(B7,INDIRECT(“‘C:UsersGW193DesktopSyd[ABC data “&E8&”.xlsx]Data’!I:M”),3,0))

The source file name is ABC data 190821A021.xlsx

=INDEX(‘c:UsersGW193DesktopSyd[ABC data “&E8&”.xlsx]Data’!$I:$O,MATCH(D8,’C:UsersGW193DesktopSyd[ABC data “&E8&”.xlsx]Data’!$I:$I,0),3)

If i use the INDEX/MATCH with the exact file name it works but when i use the cell reference to get the file name i can’t get it to work


By: Sydney

31 thoughts on “INDEX / INDIRECT

  1. suba suba

    XLmEUH This blog is obviously educating and amusing. I have picked a lot of interesting tips out of it. I ad love to visit it again soon. Thanks a bunch!

  2. pirate place pro

    Usually I do not read article on blogs, but I would like to say that this write-up very forced me to try and do it! Your writing style has been amazed me. Thanks, quite nice article.

  3. elektrotehnika

    You have made some good points there. I looked on the net for additional information about the issue and found most individuals will go along with your views on this web site.

  4. lebron shoes

    Aw, this was a very nice post. In idea I wish to put in writing like this additionally ?taking time and precise effort to make a very good article?however what can I say?I procrastinate alot and on no account appear to get one thing done.

  5. Academic

    You could certainly see your enthusiasm within the work you write. The arena hopes for more passionate writers like you who are not afraid to mention how they believe. All the time follow your heart.

Leave a Reply

Your email address will not be published. Required fields are marked *