Need a formula

[rkemper]

I need to create a formula that will convert "#N/A" to a specific value.
Specifically, cell A1 contains a VLOOKUP formula in which the value "#N/A" 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 "#N/A"
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

[John Michl]

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

- John

[Duke Carey]

Or use ISNA() instead of ISERROR()

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

"John Michl" wrote:

> Try
> =IF(ISERROR(VLOOKUP,a1,B1:C10,2,FALSE),100%,VLOOKUP,a1,B1:C10,2,FALSE)
>
> - John
>
>

[Ken Wright]

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 :-)
------------------------------*------------------------------*----------------

"John Michl" <waxwing1@gmail.com> wrote in message
news:1143574325.906543.213920@i40g2000cwc.googlegroups.com...
> Try
> =IF(ISERROR(VLOOKUP,a1,B1:C10,2,FALSE),100%,VLOOKUP,a1,B1:C10,2,FALSE)
>
> - John
>