Index/Match Array Formula to Retrieve Data from Closed Source File

[Howardjo]

Morning,

I've been using the below formula to extract data from a table stored in a source file called Central Log.xlsm.

=INDEX('Central Log.xlsm'!Table27[[#All],[Region]],MATCH($D$5,'Central Log.xlsm'!Table27[[#All],[Name]],0))

What I've been trying to do is convert this to an array formula which will complete the lookup when the source file is closed. (Currently this only populates when I have 'Central Log.xlsm' open).

I know this is possible to achieve with a simple VLOOKUP, but wondered if it was possible using INDEX/MATCH?

Regards

 

[Howardjo]

I've just found this: here

I think this will probably solve my issue (never knew this setting existed!), however, if you know of a better way of doing this it'll be greatly appriciated.

 

[Aladin Akyurek]

INDEX/MATCH and VLOOKUP is interchangeable if the look up is from left to right.

 

[Howardjo]

Thats great thanks.

For the most part I can use a VLOOKUP; but is it possible if using Index/match because the lookup is right to left?

 

[Aladin Akyurek]

Thats great thanks.

For the most part I can use a VLOOKUP; but is it possible if using Index/match because the lookup is right to left?

Yes, INDEX/MATCH can be used in any horizontal or vertical direction.

 

[Howardjo]

Sorry; I meant in reference to my original post where I am currently using Index/Match but this is not automatically updated from a closed source file (in the same way a VLOOKUP would)

 

[Aladin Akyurek]

Sorry; I meant in reference to my original post where I am currently using Index/Match but this is not automatically updated from a closed source file (in the same way a VLOOKUP would)

Care to post the formula with VLOOKUP?

 

[Howardjo]

Care to post the formula with VLOOKUP?

=VLOOKUP($D$5,'Central Log.xlsm'!Table27[#All],9,FALSE)

 

[Aladin Akyurek]

=VLOOKUP($D$5,'Central Log.xlsm'!Table27[#All],9,FALSE)

=INDEX('Central Log.xlsm'!Table27[Field-9],MATCH(D5,'Central Log.xlsm'!Table27[Field-1],0))

Substitute the header of the 9th column of Table27 for Field-9 and the header of the 1st column of Table27 for Field-1.

 

[Howardjo]

Brilliant, thank you.