VLOOKUP table array highlight problem for two excel files

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).