software

Hi,

I am using a Vlookup formula (with concatenated text) but when my
vlookup cannot find the particular word to look for, it gives me an
error message. How can I have my formula put a quot;0quot; instead of quot;N/Aquot; if
it doesn't find what it is looking for?

this is my formula:

=CONCATENATE(quot;*Of these,
quot;,ROUND(VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Report s\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0 ),quot;
(quot;,ROUND((VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Repo rts\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/C12)*100,1),quot;%)quot;,quot;
have been employed for more than 2 years, compared with
quot;,ROUND(VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\Rep orts\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0),quot;
(quot;,ROUND((VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\R eports\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/E12)*100,1),quot;%)quot;,quot; in
the Branch.quot;)

So when it doesn't find the name of the organization, I get only N/A. I
need to be able to show that there is quot;0quot; employees and be able to keep
the rest of my text, which could show something like this:

*Of these, 0 have been employed for more than 2 years, comapred to 10
(20%) in the branch.

Thanks--
Outapin
------------------------------------------------------------------------
Outapin's Profile: www.excelforum.com/member.php...oamp;userid=29278
View this thread: www.excelforum.com/showthread...hreadid=500238=IF(ISERROR(your_vlookup_formula),0,your_vlookup_f ormula)

--
Regards,
Davequot;Outapinquot; wrote:

gt;
gt; Hi,
gt;
gt; I am using a Vlookup formula (with concatenated text) but when my
gt; vlookup cannot find the particular word to look for, it gives me an
gt; error message. How can I have my formula put a quot;0quot; instead of quot;N/Aquot; if
gt; it doesn't find what it is looking for?
gt;
gt; this is my formula:
gt;
gt; =CONCATENATE(quot;*Of these,
gt; quot;,ROUND(VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Report s\Branches\Working
gt; Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0 ),quot;
gt; (quot;,ROUND((VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Repo rts\Branches\Working
gt; Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/C12)*100,1),quot;%)quot;,quot;
gt; have been employed for more than 2 years, compared with
gt; quot;,ROUND(VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\Rep orts\Branches\Working
gt; Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0),quot;
gt; (quot;,ROUND((VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\R eports\Branches\Working
gt; Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/E12)*100,1),quot;%)quot;,quot; in
gt; the Branch.quot;)
gt;
gt; So when it doesn't find the name of the organization, I get only N/A. I
gt; need to be able to show that there is quot;0quot; employees and be able to keep
gt; the rest of my text, which could show something like this:
gt;
gt; *Of these, 0 have been employed for more than 2 years, comapred to 10
gt; (20%) in the branch.
gt;
gt; Thanks
gt;
gt;
gt; --
gt; Outapin
gt; ------------------------------------------------------------------------
gt; Outapin's Profile: www.excelforum.com/member.php...oamp;userid=29278
gt; View this thread: www.excelforum.com/showthread...hreadid=500238
gt;
gt;


Thanks , but where do I put this in my formula ? Before the ROUND ?
This is where I get all mixed up with Excel formulas.... it's where to
put it...!!
Thanks--
Outapin
------------------------------------------------------------------------
Outapin's Profile: www.excelforum.com/member.php...oamp;userid=29278
View this thread: www.excelforum.com/showthread...hreadid=500238Try this:

=If(ISNA(Match($A$2,'L:\SPA_HRP\Dashboards\Reports \Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$A$30,0)),0,CONCATENATE(quot;*Of these,
quot;,ROUND(VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Report s\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0 ),quot;
(quot;,ROUND((VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Repo rts\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/C12)*100,1),quot;%)quot;,quot;
have been employed for more than 2 years, compared with
quot;,ROUND(VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\Rep orts\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0),quot;
(quot;,ROUND((VLOOKUP(quot;totalquot;,'L:\SPA_HRP\Dashboards\R eports\Branches\Working
Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/E12)*100,1),quot;%)quot;,quot; in
the Branch.quot;))

I copied this from your post, so watch out for word-wrap!
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================quot;Outapinquot; gt; wrote in
message ...
gt;
gt; Thanks , but where do I put this in my formula ? Before the ROUND ?
gt; This is where I get all mixed up with Excel formulas.... it's where to
gt; put it...!!
gt; Thanks
gt;
gt;
gt; --
gt; Outapin
gt; ------------------------------------------------------------------------
gt; Outapin's Profile:
www.excelforum.com/member.php...oamp;userid=29278
gt; View this thread: www.excelforum.com/showthread...hreadid=500238
gt;