Excel 2019 index match formula not working with multiple workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-1469056%22%20slang%3D%22en-US%22%3EExcel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469056%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20using%20the%20function%20combination%20of%20INDEX%20and%20MATCH%20to%20retrieve%20data%20from%20multiple%20workbooks%20for%20some%20time%20now%2C%20but%20now%20that%20I'm%20using%20Excel%202019%2C%20I%20cannot%20access%20other%20workbooks%20when%20I%20try%20to%20use%20the%20formula%20and%20select%20an%20array%20in%20another%20workbook.%26nbsp%3B%20This%20also%20happens%20when%20I%20try%20to%20use%20VLOOKUP%3B%20it%20won't%20let%20me%20access%20other%20workbooks%20to%20select%20the%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1469056%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469485%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701365%22%20target%3D%22_blank%22%3E%40grgrobin1206%3C%2FA%3E%26nbsp%3BI%20don't%20think%20that%20it%20is%20because%20Excel%202019.%20I%20am%20using%20Excel%202019%20and%20I%20can%20use%20Vlookup%2C%20Index%2Fmatch%20to%20find%20info%20from%20other%20closed%20workbooks%20just%20fine%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470508%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701365%22%20target%3D%22_blank%22%3E%40grgrobin1206%3C%2FA%3E%26nbsp%3Bhave%20you%20found%20a%20solution%3F%20I%20just%20upgraded%20from%202013%20to%20365%20and%20I'm%20having%20the%20same%20problem.%20I%20can%20use%20match%20and%20select%20an%20array%20in%20a%20different%20workbook...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470570%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470570%22%20slang%3D%22en-US%22%3ENo%20yet%3B%20hoping%20that%20someone%20else%20in%20the%20Tech%20Community%20has%20come%20up%20with%20a%20solution.%20Thanks%20for%20the%20feedback%2C%20hynguyen.%20Is%20it%20possible%20that%20a%20setting%20in%20Excel%202019%20needs%20to%20be%20changed%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470651%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701365%22%20target%3D%22_blank%22%3E%40grgrobin1206%3C%2FA%3E%26nbsp%3B%20I%20just%20had%20Microsoft%20tech%20support%20have%20a%20look%20for%20me.%20On%20my%20side%2C%20it%20was%20only%20an%20update%20that%20I%20had%20to%20run%20(which%20I%20should've%20probably%20checked%20first%20of%20all%2C%20but%20I%20only%20installed%20365%202%20days%20ago%2C%20so%20I%20thought%20it%20would've%20been%20the%20latest%20updates).%20I'm%20not%20sure%20if%20you%20can%20do%20updates%20with%202019%2C%20but%20maybe%20worth%20having%20a%20look.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1472841%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701365%22%20target%3D%22_blank%22%3E%40grgrobin1206%3C%2FA%3E%26nbsp%3BI%20do%20not%20think%20I%20have%20ever%20changed%20Excel%20default%20settings%20since%20installing%20it.%20Sorry%20that%20you've%20got%20that%20problem.%20Hope%20Tech%20support%20may%20help%20you%20to%20figure%20it%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474134%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474134%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20responsed%3B%20really%20appreciated.%20How%20can%20I%20contact%20Microsoft%20Tech%20support%3F%20Does%20it%20matter%20that%20this%20is%20a%20software%20installation%20on%20my%20laptop%20used%20for%20work%20and%20not%20my%20home%20pc%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474160%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20index%20match%20formula%20not%20working%20with%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474160%22%20slang%3D%22en-US%22%3EThe%20way%20I%20contacted%20them%3A%20In%20Excel%20on%20the%20top%20ribbon%20there%20is%20a%20%22Help%22%20tab%2C%20there%20is%20an%20option%20%22contact%20support%22.%20I'm%20not%20sure%20if%20it%20will%20be%20the%20same%20for%202019%20though...%20There%20might%20be%20some%20legal%20issues%20if%20it's%20for%20your%20work%2C%20I'm%20not%20sure.%20They%20remotely%20logged%20onto%20my%20laptop%20to%20do%20the%20fix%2C%20not%20sure%20if%20your%20company%20will%20allow%20that%3F%20It%20all%20was%20very%20secure%20and%20professional%20though.%3CBR%20%2F%3EBut%20all%20they%20did%20for%20me%2C%20and%20it%20work%2C%20was%20run%20an%20update....You%20can%20check%20it%20if%20you%20haven't%20yet.%20Go%20to%20File%26gt%3BAccount%26gt%3B%20then%20there%20should%20be%20an%20update%20option%2C%20just%20click%20on%20that.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I've been using the function combination of INDEX and MATCH to retrieve data from multiple workbooks for some time now, but now that I'm using Excel 2019, I cannot access other workbooks when I try to use the formula and select an array in another workbook.  This also happens when I try to use VLOOKUP; it won't let me access other workbooks to select the array.

7 Replies
Highlighted

@grgrobin1206 I don't think that it is because Excel 2019. I am using Excel 2019 and I can use Vlookup, Index/match to find info from other closed workbooks just fine

Highlighted

@grgrobin1206 have you found a solution? I just upgraded from 2013 to 365 and I'm having the same problem. I can use match and select an array in a different workbook...

Highlighted
No yet; hoping that someone else in the Tech Community has come up with a solution. Thanks for the feedback, hynguyen. Is it possible that a setting in Excel 2019 needs to be changed?
Highlighted

@grgrobin1206  I just had Microsoft tech support have a look for me. On my side, it was only an update that I had to run (which I should've probably checked first of all, but I only installed 365 2 days ago, so I thought it would've been the latest updates). I'm not sure if you can do updates with 2019, but maybe worth having a look.

Highlighted

@grgrobin1206 I do not think I have ever changed Excel default settings since installing it. Sorry that you've got that problem. Hope Tech support may help you to figure it out.

Highlighted
Thanks for the responsed; really appreciated. How can I contact Microsoft Tech support? Does it matter that this is a software installation on my laptop used for work and not my home pc?
Highlighted
The way I contacted them: In Excel on the top ribbon there is a "Help" tab, there is an option "contact support". I'm not sure if it will be the same for 2019 though... There might be some legal issues if it's for your work, I'm not sure. They remotely logged onto my laptop to do the fix, not sure if your company will allow that? It all was very secure and professional though.
But all they did for me, and it work, was run an update....You can check it if you haven't yet. Go to File>Account> then there should be an update option, just click on that.