INDIRECT inside INDEX function giving #REF error

Question

I'm have an excel workbook Spreadsheet.xlsx in which B2=MATCH(B1,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$A$500,0) takes the string (a name) in in B1 and matches it to a column of row headers in Workbook.xlsx, to give the row number.

Currently B2 returns a value of 8, which is used in B3=INDEX('\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$8:$SD$8), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0)).

B3 indexes a range of values $A$8:$SD$8 corresponding to the row number from B2 in Sheet1 in Workbook.xlsx, and returns the values matched to a date $A4 in the range $A$2:$SD$2.

Currently I am manually typing in the row number to B3 range `$A$8:$SD$8.

If I try to automate this using B3=INDEX(INDIRECT("'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet'!$A$"&B2&":$SD$"&B2), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0)), I get a #REF error.

I have used INDIRECT elsewhere e.g. =MAX(INDIRECT(K&"K1")) and it has worked just fine. Also, Workbook.xlsx and Sheet1 in it definitely exist, and I am able to get data from them without using INDIRECT.

Why doesn't it work?

Answer 1

INDEX and MATCH functions, as used here, do work with closed workbooks - INDIRECT doesn't

...but you don't really need INDIRECT here - you can use this version with B2 defining the row number in the first range

=INDEX('\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$SD$500),B2, MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0))

That should work even if the source workbook is closed, and is a "cleaner", more robust approach in any case.

Note that the first range in the formula is changed to be as long as your match range from B2

If you want you can cut out B2 altogether, just put the MATCH function from B2 in that formula in place of B2, i.e.

=INDEX('\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$SD$500),MATCH(B1,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$A$500,0), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0))

[Note: as Jerry mentioned, your sheet names weren't consistent so I changed the first to Sheet1 to match the others]