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

1,207 thoughts on “INDEX / INDIRECT


    You made some good points there. I checked on the web for more information about the issue and found most people will go along with your views on this site.

  2. um learn login

    Thanks for your write-up. I also think laptop computers are getting to be more and more popular nowadays, and now are sometimes the only sort of computer used in a household. The reason being at the same time that they are becoming more and more cost-effective, their computing power keeps growing to the point where there’re as highly effective as personal computers from just a few years back.

Comments are closed.