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

One thought on “INDEX / INDIRECT

Leave a Reply

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