How do I remove #N/A value from VLOOKUP

[PattiK323]

VLOOKUP(A4,'4PM_JAN16 MTD'!$A$3:$AC$101,29,FALSE)

I'm getting some #N/A values when N/A is not in the $A$3:$AC$101 range. How do I modify the formula so it just shows a blank, instead of #N/A, if A4 does not appear in the range that I'm trying to return a value from?

 

[Joe4]

If you are using a newer version of Excel that has the IFERROR function, just use:

[COLOR=#333333]=IFERROR(VLOOKUP(A4,'4PM_JAN16 MTD'!$A$3:$AC$101,29,FALSE),"")[/COLOR]

Otherwise, you can use:

=IF(ISNA([COLOR=#333333]VLOOKUP(A4,'4PM_JAN16 MTD'!$A$3:$AC$101,29,FALSE)),"",[/COLOR][COLOR=#333333]VLOOKUP(A4,'4PM_JAN16 MTD'!$A$3:$AC$101,29,FALSE))[/COLOR]

 

[PattiK323]

ISERROR worked! Thanks so much!

 

[PattiK323]

Another question:

I have this formula:

=F4-$D4

Where F is sometimes blank, as per the IFERROR(VLOOKUP....) formula previously posted about. That produces a #VALUE! error for F4-$D4. How do I make this blank instead of #VALUE? I tried applied the same IFERROR formula; but, it didn't work.

 

[Joe4]

[COLOR=#333333]=F4-$D4[/COLOR]

will not produce a #VALUE error if F4 is really blank or empty.
However, it will produce that error if there is some non-numeric entry, like blank spaces in F4.
So, what is really in F4?

BTW, if it is just spaces, the following does work for me:

=IFERROR(F4-D4,"")