Do I need to open the source file to fetch source data by using INDEX/MATCH

[BNCOXUK]

Hi guys,

I want to use the following INDEX/MATCH combination to fetch data from an external workbook as:

=INDEX('C:\Temp\Example.xlsm'!Score,MATCH(A1,'C:\Temp\Example.xlsm'!IDCode,0))

Do you think this formula will work? I am just not sure if the source file C:\Temp\Example.xlsm must be opened first.

[Glenn Kennedy]

What happened when you tried it yourself?

[Pete_UK]

The filename (and extension) should be inside square brackets in both cases.

It is easier to set up references like these if both files are open initially, and then as you are typing in the formula you should just click over to the window of the source file and select your range(s) from there - Excel will automatically use the correct syntax, although it will initially not show the full path if both files are open. Then when you close the source file the path will be included within the formula.

Hope this helps.

Pete

[BNCOXUK]

Many thanks, Glenn and Pete.

So, do you think that formula will work, without having to open the source file first? I tried myself, it seems working. But I am not really sure, as I used VLOOPUP before which failed to (the source file has to be opened first!)

[Glenn Kennedy]

I was trying to encourage you to try it out first, rather than asking the question without having experimented yourself.
However, I do know that it won't work unless you put square brackets round the filensme.xlsx. Then it will work. .

[BNCOXUK]

Yes, Glenn. I tried it out. It seems that it does not work. So what is the job of the square brackets? That is really interesting to me.

[Pete_UK]

That is the syntax that Excel imposes - the filename needs to be inside square brackets. Did you try my method of generating the links by clicking on the appropriate ranges?

Pete

[hrlngrv]

Your formula should work as long as Score and IDCode are workbook-level defined names in C:\Temp\Example.xlsm.

If it's not working, why not follow the recommendation in the yellow box as the top of every page on this site and include example workbooks demonstrating the problem?

In contrast, something like this works for me. Download both files into the same directory.

[hrlngrv]

The filename (and extension) should be inside square brackets in both cases. . . .

Wrong for workbook-level defined names.

[BNCOXUK]

Yes, Pete. I agree the links can be updated correctly by Excel when the source file is closed.

[BNCOXUK]

I tried again and it seems that it does work, as confirmed by hrlngrv.

Besides, the brackets around the file name are NOT needed. This would be such an easy way to fetch data from external sources without having to open the source file?