software

I need to create a formula that will convert quot;#N/Aquot; to a specific value.
Specifically, cell A1 contains a VLOOKUP formula in which the value quot;#N/Aquot; is
displayed when the formula is FALSE (the lookup value is not found), which is
correct.

I need to create another formula that will automatically convert #N/A to
100%, but leave any other values as is. For example, if A1 displays quot;#N/Aquot;
then I need it to be converted to read 100%. But if A1 displays 70% then I
need it to remain the same.
--
Thanks!

RKEMPER

Try
=IF(ISERROR(VLOOKUP,a1,B1:C10,2,FALSE),100%,VLOOKU P,a1,B1:C10,2,FALSE)

- JohnOr use ISNA() instead of ISERROR()

=if(isna([vlookup formula or cell reference]), 1,[vlookup formula or cell
reference])

quot;John Michlquot; wrote:

gt; Try
gt; =IF(ISERROR(VLOOKUP,a1,B1:C10,2,FALSE),100%,VLOOKU P,a1,B1:C10,2,FALSE)
gt;
gt; - John
gt;
gt;

If the error is #NA then it should be ISNA used, else ISERROR may hide other
inadvertant errors.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

quot;John Michlquot; gt; wrote in message oups.com...
gt; Try
gt; =IF(ISERROR(VLOOKUP,a1,B1:C10,2,FALSE),100%,VLOOKU P,a1,B1:C10,2,FALSE)
gt;
gt; - John
gt;