External VLOOKUP returning #value

[20juls]

hi,
I made a VLOOKUP to a different workbook and it returns #value when the othe workbook is closed

however each vlookup by it self returns the correct value
The goal is to retrive data to a given firm from the Leads database and if there is no data, the next step is to look in M&A database

=IF(COUNTA(D21)=1,IF(COUNTIF('G:\06 Online\[Internet Lead List.xlsm]Leads'!$D$13:$D$7000,D21)>0,VLOOKUP(D21,'G:\06 Online\[Internet Lead List.xlsm]Leads'!$D$13:$AK$7000,25,FALSE),VLOOKUP(D21,'G:\06 Online\[Internet Lead List.xlsm]M&A'!$C$13:$W$7000,4,FALSE)),"")

Does anyone know whats wrong with my formular?

Thanks
Julius

[dilipandey]

Hi Julius,

Vlookup works on closed workbooks.. try including full file path in the formula. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Richard Buttrey]

I can't see anything wrong with the formula.

Have you checked to confirm that none of the values in the both the first column of the ranges being looked or the 25th or 4th column of the relevant ranges is showing an error?

[martindwilson]

countif doesnt work on closed work books
http://support.microsoft.com/kb/260415

[Richard Buttrey]

countif doesnt work on closed work books
http://support.microsoft.com/kb/260415

Thanks Martin,

They say you learn something new every day. You've just demonstrated that this is one of those days.
Cheers

[20juls]

thank you very much thats great i would have never thought of that. now i have to reathink how i can achieve the same without countif

[dilipandey]

Thanks to martin for 'heads-up'

20juls, please mark this thread as [SOLVED].. thanks.



Regards,
DILIPandey
<click on below * if this helps>