Forum Discussion

Mitul123's avatar
Mitul123
Copper Contributor
Oct 28, 2025

VLOOKUP table array highlight problem for two excel files

I have two excel files. Book3 & Book2
In Book3 i type VLOOKUP there table array comes from Book2. But when by F2 I open formula the table array highlight in Book3, but actually it comes from Book2. So how to solve this problem?

This is file Book3

This is file Book2

community

1 Reply

  • Surya_Narayana's avatar
    Surya_Narayana
    MCT
    Nov 16, 2025

    hi Mitul123​ check below steps and try

    Rebuild the Table Array Reference Correctly

    Open both files:

    Book2.xlsx

    Book3.xlsx

     

    In Book3, edit your VLOOKUP:

    =VLOOKUP(A2,

    Now switch to Book2 and highlight the lookup table → Excel should insert:

    =[Book2.xlsx]Sheet1!$A$2:$D$200

    Press Enter.

    Now pressing F2 will highlight the correct table in Book2.

     

    2: Ensure External Links Are Enabled

    Go to:

    File → Info → Enable Editing

    File → Options → Trust Center → Trust Center Settings → External Content

    ✓ Enable “Update links to other documents”

     

    3: Do NOT rename or move Book2

    If the file path breaks, Excel removes the external reference and replaces it with a local range.

     

    4: Use FULL PATH if needed

    If files are in different folders, use:

    =VLOOKUP(A2,'C:\Users\...\Documents\[Book2.xlsx]Sheet1'!$A$2:$D$200,3,FALSE)

     

    Test if Excel still sees Book2 as an external workbook

    Go to:

     

    Data → Edit Links

    If Book2 does not appear, the formula no longer contains an external reference → must be rebuilt (Fix 1).

Resources