VLOOKUP doesn't show values from closed workbook

[imfreakingout]

This is the first time I've used the VLOOKUP function. After playing with it for a while to make sure I understood it, I created 2 workbooks. One is my master information with daily information on separate worksheets by day. The other is a file that compiles the daily information into monthly totals based on a key.

I can get the information to populate correctly if both workbooks are open. However, if the "master" workbook is closed then the other workbook with the VLOOKUP functions displays "VALUE?" errors for every cell with that function.

How can I get the secondary workbook(s) to read the information from the closed "master"?

 

[Fergus]

VLOOKUP works to closed workbooks, so that is not the root cause of your problem. Can you post the formula you are using and a bit more detail or snapshots using Colo's HTML Maker (see link on Forum Page)

 

[imfreakingout]

Each cell has a slightly different formula since they pull different data. However, the basic formula is:

=IF(COUNTIF('\\Phxfs01\city\Supervisors\Mark\\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,A2), VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE), "")

 

[dcardno]

I think the problem is in the "Countif" Function: when I try to use a range on a closed workbook as the range to be tested it returns a "#Value" error. Since you are using this to prevent an error in the value returned by the Vlookup function, perhaps you should use a different formulation, like:

=if(iserror(VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE), "",VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE))

(I'm not sure if I got the brackets right - I was just cutting & pasting...)

 

[Fergus]

I agree with Dean that COUNTIF will give an error when the range is in a closed workbook. But another point I noticed is that your original COUNTIF will count all incidents of A2 in the entire range B5:AE200, but the following VLOOKUP will only return an answer for the first occurence of A2 in column B (the first column of the lookup range) and the answer returned will be whatever is in column C (the second column of the lookup range) on that row. So your IF(COUNTIF .... will be true if it finds A2 in say cell AB195 thereby triggering the VLOOKUP but then no A2 will be found in column B so the result will be #VALUE.

If what you are trying to do is first check for the existence of A2 in the first column of your lookup range before executing the VLOOKUP then Deans solution will work or your could also try

=IF(ISNA(MATCH(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$B$200)),"",VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE)

I did not test this, but I think the brackets etc are right

 

[imfreakingout]

We're getting close. Thanks for your help. Here's where I'm at now. When I enter the value 6201 in A2 (which is the value I've been using for testing), everything works fine. When I change the value to something else (e.g. 6233), the data is populated, but it also returns #N/A errors where data wasn't found. This only happens on A2 values other than the original 6201 I have used for testing when I created the worksheet.

Here is a copy of the formula when A2 = 6203:
=IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$B$900,A2)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$AE$900,2,FALSE))

Here is a copy of the formula from the same cell when A2 = 6233:
=IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$B$900,A2)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$AE$900,2,FALSE))

The file path mentioned in my original posting is not shown here because I had both workbooks opened when I copied the formulas.

I cannot use Colo's html maker here. However, I did create jpegs of them if that would help.

Thanks again!

 

[Fergus]

try: =IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$2:$B$900,0)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$2:$AE$900,2,0))

 

[imfreakingout]

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU.....

Saying thanks doesn't seem like enough. I stared at the formula for hours and finally saw a tiny flaw, changed it and now everything works perfectly. Thank you so much to each of you who helped me with this problem!