Share via

VLOOKUP between SharePoint & local files

Anonymous
2025-06-05T18:17:27+00:00

Hi

Is it possible to do a VLOOKUP between a file that is saved on SharePoint & a file that is saved on my desktop. When I try doing this I am unable to select the columns.

Thanks

Esha

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2025-06-05T19:21:53+00:00

    Hi Esha Jones, 

    Good day to you! Thank you for reaching out to Microsoft Community. 

    In order to use the VLOOKUP formula between 2 excel files, please take the following steps: 

    1. Open the SharePoint file in Excel: 
    2. Use the full path in the VLOOKUP formula in file A, referencing file B: =VLOOKUP(A2, '[https://yourcompany.sharepoint.com/sites/YourSite/SharedDocuments/FileB.xlsx]Sheet1'!$A$2:$B$100, 2, FALSE), in which: 
      • A2: The value you’re looking up.
      • '[https://yourcompany.sharepoint.com/sites/YourSite/SharedDocuments/FileB.xlsx]Sheet1'!$A$2:$B$100: The range in File B where the lookup should happen.
      • 2: The column number in the range from which to return the value.
      • FALSE: Ensures an exact match.
    3. Authentication 
      • You must be authenticated to SharePoint (usually via Microsoft 365 login).
      • If you're not signed in, Excel may prompt you to log in or fail to load the data.

    This is the result after taking the steps above:  

    Hi Esha Jones, 

    Good day to you! Thank you for reaching out to Microsoft Community. 

    In order to use the VLOOKUP formula between 2 excel files, please take the following steps: 

    1. Open the SharePoint file in Excel: 
    2. Use the full path in the VLOOKUP formula in file A, referencing file B: =VLOOKUP(A2, '[https://yourcompany.sharepoint.com/sites/YourSite/SharedDocuments/FileB.xlsx]Sheet1'!$A$2:$B$100, 2, FALSE), in which: 
      • A2: The value you’re looking up.
      • '[https://yourcompany.sharepoint.com/sites/YourSite/SharedDocuments/FileB.xlsx]Sheet1'!$A$2:$B$100: The range in File B where the lookup should happen.
      • 2: The column number in the range from which to return the value.
      • FALSE: Ensures an exact match.
    3. Authentication 
      • You must be authenticated to SharePoint (usually via Microsoft 365 login).
      • If you're not signed in, Excel may prompt you to log in or fail to load the data.

    This is the result after taking the steps above:  

    Note 

    • Make sure the file paths and sheet names are correct.
    • If File B is moved or renamed, the formula will break.
    • For better performance and flexibility, consider using Power Query or INDEX/MATCH as alternatives
    • Both files must be open in Excel for the formula to resolve easily.

    If you have any further questions, please don’t hesitate to ask. Looking forward to your response. 

    Best regards, 

    Jay-Tr– MSFT | Microsoft Community Support Specialist 

    Note 

    • Make sure the file paths and sheet names are correct.
    • If File B is moved or renamed, the formula will break.
    • For better performance and flexibility, consider using Power Query or INDEX/MATCH as alternatives
    • Both files must be open in Excel for the formula to resolve easily.

    If you have any further questions, please don’t hesitate to ask. Looking forward to your response. 

    Best regards, 

    Jay-Tr– MSFT | Microsoft Community Support Specialist

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-09T18:59:57+00:00

    Hi Esha Jones, 

    It has been a while and I am writing to see how things are going with this issue. Have you had a chance to check the replies provided? Any update would be appreciated.

    Best regards, 

    Jay-Tr– MSFT | Microsoft Community Support Specialist

    0 comments