Need Someone with Better Eyes - Index Indirect

[luke1438]

I am trying to pull data from another workbook with a certain sheet name. The certain sheet name is in cell J1 within Workbook 1. The data I need in Workbook 1 cell J4 is in Workbook 2 cell O35. I am using the following formula:

INDEX(INDIRECT("'[Workbook 2.xlsx]"&J1&"'!"&$A$3:$O$40),MATCH($G4,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$3:$O$3"),0),MATCH($L$2,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$4:$A$40"),0))

The two MATCH/INDIRECT parts of the formula works fine, but it is the first part; INDEX(INDIRECT that is not. I clearly have something wrong because the range $A$3:$O$40 is highlighting in Workbook 1 when I click on the formula. I really need it to index Workbook 2.

If anyone sees my mistake please let me know what I have wrong where, please!

Thanks
Tim

 

[Fluff]

The $A$3:$O$40 range should be in quotes.

 

[Peter_SSs]

INDEX(INDIRECT("'[Workbook 2.xlsx]"&J1&"'!"&$A$3:$O$40),MATCH($G4,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$3:$O$3"),0),MATCH($L$2,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$4:$A$40"),0))

The two MATCH/INDIRECT

They may 'work', but from what I can see they are the wrong way around. In an INDEX function, the 2nd argument is the row number and the 3rd argument is the column number. Therefore the red and blue sections need to be swapped.

The orange part is what @Fluff was referring to but it also is the wrong range. It should be $A$4:$O$40

So, try this

=INDEX(INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$4:$O$40"),MATCH($L$2,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$4:$A$40"),0),MATCH($G4,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$3:$O$3"),0))

 

[luke1438]

Thank you so much. Very good catch to both of you. Sometimes writing the formula I think of the information I want first and don't think about how the formula should be written.
Thanks again
Tim

 

[luke1438]

Solution
INDEX(INDIRECT("'[Workbook 2.xlsx]"&J1&"'!"&"$A$3:$O$40"),MATCH($L$2,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$3:$A$40"),0),MATCH($G4,INDIRECT("'[Workbook 2.xlsx]"&J1&"'!$A$3:$O$3"),0))