1. Open the source file.
2. Build you vlookup.
3. Close the source file.
The links names will changes themselves to the correct name and path automatically. As long as nobody moves it, you'll never have to open the source sheet again.
=VLOOKUP(B1,'[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,TRUE)
=VLOOKUP(B1,'c:\path\[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,TRUE)
I disagree about using TRUE though. I generally want an EXACT match so use FALSE for the argument that says Excel can approximate it. TRUE is used when you are looking up a value in a range of numbers, such as a tax table. In those cases, the lookup column also needs to be sorted.
=VLOOKUP(B1,'C:/Some Folder/Some subfolder/[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,FALSE)
=VLOOKUP(A6,IndirectEx("'C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes'!MatlDB"),2,FALSE)
Title | # Comments | Views | Activity |
---|---|---|---|
HOW D I CLEAR PICTURES IN IMAGE CONTROL AND COMMENTS IN CELLS WHEN WORKBOOK IS OPENED | 18 | 47 | |
Multi vLookup (Excel vba) | 12 | 26 | |
Mac-based software for Excel | 8 | 19 | |
Excel- VBA help on macros that size columns and rows | 7 | 21 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
7 Experts available now in Live!