Match , Index and Indirect

[vmjan02]

I have file and in column J2 & K2, so need a sigle formaul to apply to all the sheets not sure how to do it but is ispossible

J2 this correct

=INDEX(Cisco_0.csv!$A:$H,MATCH(X2,Cisco_0.csv!$E:$E,0),1)

K2 this is corret

=INDEX(Cisco_0.csv!$A:$H,MATCH(X2,Cisco_0.csv!$E:$E,0),8)

above 2 formuls works all fine, but there are around (Cisco_0.csv!) 0 to 9 csv files and names have added a sheet1 with all file names as list, so that that list will check .csv0 to .csv 9 and give the result in J2 and K2 respectively.

Golden Automation AFinal V1.xlsm
	A
1	List of FileNames
2	[Cisco_0.csv]Cisco_0'!
3	[Cisco_1.csv]Cisco_1.csv!
4	[Cisco_2.csv]Cisco_2.csv!
5	[Cisco_3.csv]Cisco_3.csv!
6	[Cisco_4.csv]Cisco_4.csv!
7	[Cisco_5.csv]Cisco_5.csv!
8	[Cisco_6.csv]Cisco_6.csv!
9	[Cisco_7.csv]Cisco_7.csv!
10	[Cisco_8.csv]Cisco_8.csv!
11	[Cisco_9.csv]Cisco_9.csv!
Sheet1

this modified formual in J2 is not working.

=INDEX(INDIRECT(INDEX(Sheet1!$A$2,MATCH(X2,Sheet1!$E$2:$E$11, 0))&"'!$A:$H"),MATCH(X2, INDIRECT(INDEX(Sheet1!$A$2,MATCH(X2,Sheet1!$E$2:$E$11, 0))&"'!$E:$E"), 0),1)

 

[6StringJazzer]

What does "not working" mean? What result do you expect, and what result are you getting?

INDIRECT using to reference an external file will return a #REF! error unless the external file is open. Is that your situation?

 

[vmjan02]

the result is #N/A , but if I apply it single file, it is givine me match result and not #N/A
Idea is to get all the files in one formula, or eelse I have to keep on applying 9 differnet formulas so was trying to do indirect as well.

 

[vmjan02]

ay way out of this

 

[vmjan02]

What does "not working" mean? What result do you expect, and what result are you getting?

INDIRECT using to reference an external file will return a #REF! error unless the external file is open. Is that your situation?

if the file is open or closed it is giving me #NA, but to apply on single file 0 to 9, it is giving me required result.

 

[vmjan02]

File and sheet name

Golden Automation AFinal V1.xlsm
	A	B	C
1	List of FileNames	Sheet Name	File&Sheet
2	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_0.csv]	Cisco_0'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_0.csv]Cisco_0'!
3	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_1.csv]	Cisco_1'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_1.csv]Cisco_1'!
4	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_2.csv]	Cisco_2'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_2.csv]Cisco_2'!
5	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_3.csv]	Cisco_3'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_3.csv]Cisco_3'!
6	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_4.csv]	Cisco_4'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_4.csv]Cisco_4'!
7	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_5.csv]	Cisco_5'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_5.csv]Cisco_5'!
8	C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_6.csv]	Cisco_6'!	'C:\Viral Shah\Automation\Golden Automation\RawData NE\Aggregator\Cisco InMac Agg\[Cisco_6.csv]Cisco_6'!
Reference

Cell Formulas
Range	Formula
C2:C8	C2	="'"&A2&B2

can Cisco_0.csv! be replaced by C2
INDEX(Cisco_0.csv!$A:$H,MATCH(X2,Cisco_0.csv!$E:$E,0),1)