SumProduct Error

[mrauch]

Hello

I have this formuala which works.

=INDEX('[Master of Attributes.xlsx]Master Summary'!$B$9:$DB$371,MATCH($A4,'[Master of Attributes.xlsx]Master Summary'!$B$9:$B$371,0),MATCH(P$3,'[Master of Attributes.xlsx]Master Summary'!$B$8:$DB$8,0))

Its is an Index Match formula linked to another file. However, I would like to get the same result this index match formaula gives - except I want to use a sum product becasue sum product will go into a closed file and retrieve the data and index match requires me to have the source file open.

I have tried to build the sumproduct and it gives me an error and I do not knwo why. Please can you identify my error.

=SUMPRODUCT(('[Master of Attributes.xlsx]Master Summary'!$B$9:$AZ$370)*('[Master of Attributes.xlsx]Master Summary'!$B$9:$B$370=A4)*('[Master of Attributes.xlsx]Master Summary'!$B$8:$AZ$8=P3))

Thanks
M

 

[Fluff]

Hi & welcome to MrExcel.
That index match formula should work on a closed workbook.
What makes you think it doesn't?

 

[mrauch]

Hi Fluff- someone told me. I guess that is the starting point to my question.
Do I need to have the source data workbook open - in order to update a index match formula?

 

[Fluff]

Not that I'm aware of. As long as you use ranges (as in you op) rather than named ranges or structured table references, you should be ok.