Need a formula

R

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.
 
J

John Michl

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

- John
 
D

Duke Carey

Or use ISNA() instead of ISERROR()

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

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 :)
------------------------------­------------------------------­----------------
 
Top