Hiding an #N/A error

I have a cell with the following formula

=VLOOKUP(Sheet1!A14,Sheet3!A5:Sheet3!K150,11,FALSE)

If Sheet1!A14 is not present on Sheet3! I will get an #N/A error. I have another cell on a summary sheet that returns the data from the above formula. In order to clean up the summary sheet I only want data returned, if #N/A is a result of the formula I do not want any info on the summary sheet. I tried the following formula but it did not work, is there another way to do it?

=IF(P12="#N/A","",P12)

My hope was that I could replace #N/A with ""

Hi

try

=if(isna(VLOOKUP(Sheet1!A14,Sheet3!A5:Sheet3!K150,11,FALSE )),"",VLOOKUP(Sheet1!A14,Sheet3!A5:Sheet3!K150,11,FALSE ))


rylo

That tip worked to leave the cell I wanted blank but it left a #value! in my totals cell because the totals cell is now adding ""+0 instead of 0+0. One fix created another problem. Here are the formulas as I have them now.

Cell H14

=IF(ISNA(VLOOKUP(Sheet1!A14,Sheet3!A5:Sheet3!K150, 11,FALSE )),"",VLOOKUP(Sheet1!A14,Sheet3!A5:Sheet3!K150,11, FALSE ))

Cell J14 is entered by hand

Cell L14 (Total of H14+J14)

=IF(SUM(H14+J14)=0,"",SUM(H14+J14))

Before the formula for the total cell worked because all the info was manually entered. It does not bother me that I get a #VALUE! error in the totals cell, I just don't want to see it. Any ideas?

You get the error when you try to add a number to a formula blank [""] but you don't need to do that, SUM will ignore text so change to

=IF(SUM(H14:J14)=0,"",SUM(H14:J14))