This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Does anyone have a good simple example file that does a VLOOKUP to an external unopened workbook? I am looking to pull one value to an Activesheet, but do not want to open the huge file that the value is in... Any help would be appreciated.

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

Obviously you will replace the `$B$2:$C$62` with the location of the data you are looking up and the "2" with what column you want returned. And "B1" with what column in the active workbook you are testing against.

Brian makes it easy to create the formula by opening the extra workbook and using the Formulas tab to insert a formula excel will do the typing for you. Make sure your last value in the VLOOKUP is "TRUE" as using "FALSE" often gives undesired results as it looks for an exact match while "TRUE" looks for one that is a close match

```
=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.
Using TRUE is done mostly when you are doing bracket price lookups, such as with income tax tables, or when the unit price of a commodity varies with how many you buy. In such cases, the leftmost column in the lookup table must show the bottom of each bracket, and the brackets must be listed in ascending order.

I have occasionally recommended VLOOKUP with TRUE as the fourth parameter when I needed an exact match and knew that it would always be in the lookup table. The benefit here is more efficient searching if you have previously sorted sorting the lookup table by the first column values. By using TRUE as the fourth VLOOKUP parameter, VLOOKUP performs a binary search for the right row, and can find the right answer in a 1000 row table with only 11 tests. In contrast, the same VLOOKUP search with FALSE as the fourth parameter will test 500 times (on average) before finding a match.

If you like the speed of TRUE, but need to allow for the possibility of not matching, then you could use two VLOOKUP calls:

=IF(VLOOKUP("LookFor", A$1:A$1000,1)="LookFor",VL

You will note that the above formula omits TRUE as the fourth VLOOKUP parameter; TRUE is assumed in such omissions.

Finally, you need to know the path to the workbook if you don't want to open it while performing a VLOOKUP. Michael McNair's formula should therefore be:

```
=VLOOKUP(B1,'C:/Some Folder/Some subfolder/[Workbookname.xlsx]SheetName'!$B$2:$C$62,2,FALSE)
```

Although I could certainly attach two workbooks as requested to illustrate the results of this process, the formulas probably won't work when you download the workbooks. Unless you put the closed file in the same folder & path that file lives in on my computer, the VLOOKUP formula in the open workbook will return an error value until you correct the path.

Your example uses: =VLOOKUP(A8,IndirectEx($G$

C:\_Excel\ExcelExamples\VL

=VLOOKUP(A8,IndirectEx(C:\

it fails. Having issues rewriting the formula, any ideas?

I am also confused about whether I really need to use IndirectEx? Sounds like from the other posts, that is not necessary? Would like some more guidance please.

=VLOOKUP(A12,'C:\_Excel\Ex

If you don't want the indirectex to refer to a cell instead you can put the path inside the double quote " full path and address"

However please note that you must use theindirectex UDF , if you remove the UDF it will not work , it may return the data which is saved in memory, but if you change the path it will not work. So you need to have the UDF Indirectex and either use cell eefer CEO or hardcode the full path and address inside it wrapped by double quote.

=VLOOKUP(A12,IndirectEx'C:

Still need some help writing the whole thing out.. does not seem to want to work with the IndirectEx in it.

```
=VLOOKUP(A6,IndirectEx("'C:\_Excel\ExcelExamples\VLOOKUP_External_Sample\[vlookup_with_indirect_source.xlsx]Bikes'!MatlDB"),2,FALSE)
```

Seems to have worked.. now just not understanding the need for or not the need for IndirectEx..

INDIRECTEX is the main driver for fetching the data from the closed workbook. it is not the standard INDIRECT function, it is a user defined function meaning it is a created function. if you press Alt + F11 you will see the UDF code . if you eliminate INDIRECTEX then it will not work.

i am glad that it worked for you.

if you change value in external workbook,. then you need save it and close it and then open the workbook with formula and the changed values should reflect in the workbook with formula.

the formula works when you change something in ext workbook and then save and close it and open the workbook with formula again.

also, make sure that your macro is enabled when you open the workbook with formula.

If those parameters are not fixed then an INDIRECT function can be used to create the name and/or path but INDIRECT only works on open workbooks, hence ProfJJ suggestion to use a UDF to overcome the restriction of INDIRECT.

If you create a link to a smaller file, you can then use the same syntax "Path/File/Sheet/Range" to amend for the file you don't want to open, so long as you know the parameter values to enter.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

please see my answer to similar question at post here

i believe, that is the best ready made example that can get you started.

download the two files, one which is a source file and the otherone which has the code in it. change the file path and the see the example of vlookup on the closed file. it will work without opening the source file.