Converting #N/A to other values

[Ming Ha Chok]

Hi,

I am an xls user for many years but never had the need to use formula. Today, my job requires me to do some mapping and files comparison. After I have performed VLOOKUP, I got the result as #N/A. Now, I want to know how to convert #N/A to "Blank".
Please share with me.

Thank you

Ming Ha

[ConneXionLost]

Hi Ming Ha,

The #N/A is usually avoided with an IF formula to check whether the value you need is in the list before you let VLOOKUP do it's work. COUNTIF is useful for this as well. For example:

=IF(COUNTIF(A1:A100,G2),VLOOKUP(G2,A1:C100,3,0),"")

This formula counts the number of times the value of G2 is in column A. If it isn't there, the formula returns "", or nothing. Otherwise it's business as usual for the VLOOKUP with the assurance the value will be found.

[Ming Ha Chok]

Hi Ming Ha,

The #N/A is usually avoided with an IF formula to check whether the value you need is in the list before you let VLOOKUP do it's work. COUNTIF is useful for this as well. For example:

=IF(COUNTIF(A1:A100,G2),VLOOKUP(G2,A1:C100,3,0),"")

This formula counts the number of times the value of G2 is in column A. If it isn't there, the formula returns "", or nothing. Otherwise it's business as usual for the VLOOKUP with the assurance the value will be found.

Hi,

Thanks for the prompt response. My formula is this: =VLOOKUP(A:A,'ABC'!A:C,3,FALSE). The result I got is #N/A. Now I want the result to show 'Blank. Can you please show me the formula.

[xwarlock10x]

just put the IFERROR in front of the vlookup and at the end put a , the "" with a )

something like this =IFERROR(VLOOKUP($E2,'Pull From '!$A$2:$C$13,COLUMNS($A$2:A2)+1,0),"")

[Ming Ha Chok]

Thank you xwarlock10x, your answer works. How about this one" =VLOOKUP(A:A,'Model Office'!A:B,2,FALSE)" ? The value is matched and returned a "0" (zero value). How do I convert it to 'Blank'?

[ConneXionLost]

xwarlock10x,

Ming Ha's profile suggests Excel 2003; IFERROR won't work.

[FDibbins]

xwarlock the OP's profile suggests they have 2003, iferror() only became available in 2007. So they would need to use...

=IF(iserror(VLOOKUP($E2,'Pull From '!$A$2:$C$13,COLUMNS($A$2:A2)+1,0),"",VLOOKUP($E2,'Pull From '!$A$2:$C$13,COLUMNS($A$2:A2)+1,0))

formula modified...
=if(iserror(=VLOOKUP(A:A,'ABC'!A:C,3,FALSE),"Blank",=VLOOKUP(A:A,'ABC'!A:C,3,FALSE))

[ConneXionLost]

Yes, ISERROR can be used; however, I suggest COUNTIF because it is more efficient than ISERROR(VLOOKUP.

[FDibbins]

Ming Ha, what version of excel are you using? If is it not 2003, I suggest you update your profile

Also, take a look at post #6