# Formula to make sure there is no #N/A

• I have this big macro and Part of it is the Vlookup formula filled down for the rest of a column. I customized the formula with IF statement so that it can take out the #N/A that shows up when vlookup can't find the value.

Code
``=IF(VLOOKUP(B3,AUM!\$A\$5:\$E\$53,2,FALSE)="#N/A","",(VLOOKUP(B3,AUM!\$A\$5:\$E\$53,2,FALSE)))``

Its giving me the correct value but it is still not taking out the #N/A and leaving it blank. I need it to leave those cells blank since I need to total it at the end. And I can't get the sum function to work if there are #N/A in that Column.

Any solutions and help is greatly appreciated.

Q

• Re: Formula to make sure there is no #N/A

Try this

=if(isna(vlookup(B3,AUM!\$A\$5:\$E\$53,2,False)),0,vlookup(B3,AUM!\$A\$5:\$E\$53,2,False))

Andy

• Re: Formula to make sure there is no #N/A

That was perfect ... thanks alot
Q

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!