Vlookup - help

[Outapin]

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 "0" instead of "N/A" if it doesn't find what it is looking for?

this is my formula:

=CONCATENATE("*Of these, ",ROUND(VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Reports\Branches\Working Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0 )," (",ROUND((VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Reports\Branches\Working Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/C12)*100,1),"%)"," have been employed for more than 2 years, compared with ",ROUND(VLOOKUP("total",'L:\SPA_HRP\Dashboards\Reports\Branches\Working Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0)," (",ROUND((VLOOKUP("total",'L:\SPA_HRP\Dashboards\Reports\Branches\Working Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/E12)*100,1),"%)"," in the Branch.")

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 "0" 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

[David Billigmeier]

=IF(ISERROR(your_vlookup_formula),0,your_vlookup_formula)

--
Regards,
Dave


"Outapin" wrote:

>
> 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 "0" instead of "N/A" if
> it doesn't find what it is looking for?
>
> this is my formula:
>
> =CONCATENATE("*Of these,
> ",ROUND(VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Reports\Branches\Working
> Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0 ),"
> (",ROUND((VLOOKUP($A$2,'L:\SPA_HRP\Dashboards\Reports\Branches\Working
> Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/C12)*100,1),"%)","
> have been employed for more than 2 years, compared with
> ",ROUND(VLOOKUP("total",'L:\SPA_HRP\Dashboards\Reports\Branches\Working
> Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE),0),"
> (",ROUND((VLOOKUP("total",'L:\SPA_HRP\Dashboards\Reports\Branches\Working
> Dashboards\[Terms.xls]Terms'!$A$6:$D$30,3,FALSE)/E12)*100,1),"%)"," in
> the Branch.")
>
> 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 "0" 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: http://www.excelforum.com/member.php...o&userid=29278
> View this thread: http://www.excelforum.com/showthread...hreadid=500238
>
>

[Outapin]

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

[RagDyer]

Try this:

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

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!
==============================================


"Outapin" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
http://www.excelforum.com/member.php...o&userid=29278
> View this thread: http://www.excelforum.com/showthread...hreadid=500238
>