replace N/A with blank

[leeners82]

I need to create a vlookup function where the following occur:
1) the function looks for a number in the worksheet and if that cell i
blank the cell with the function is also left blank.
however,
2) if there is a number in the referenced cell I need a function t
lookup that same number in another spreadsheet (vlookup function). On
catch...if the number that should be inserted from the lookup is 0 o
if the cell is blank, i want the function cell to also be left blank.
Here is the current function:

=IF(D11="","",VLOOKUP(D11,'[International Breakdown Month.xls]Re
ss'!$A$7:$L$29,8,FALSE)

 

[Guest]

=IF(D11="","",IF(OR(VLOOKUP(D11,'[International Breakdown Month.xls]Rec ss'!$A$7:$L$29,8,FALSE)="",VLOOKUP(D11,'[International Breakdown Month.xls]Rec ss'!$A$7:$L$29,8,FALSE)=0,ISERROR(VLOOKUP(D11,'[International Breakdown Month.xls]Rec ss'!$A$7:$L$29,8,FALSE))),"",VLOOKUP(D11,'[International Breakdown Month.xls]Rec ss'!$A$7:$L$29,8,FALSE)))

 

[Tushar Mehta]

Option 1: Suppose you have the formula in cell X1. Then, in Y1, enter
=IF(ISNA(X1),"",X1). For aesthetic reasons, you could hide column Y.

Option 2: Use
=IF(D11="","",if(ISNA(VLOOKUP(D11,'[International Breakdown Month.xls]
Recss'!$A$7:$L$29,8,FALSE)),"",VLOOKUP(D11,'[International Breakdown
Month.xls]Recss'!$A$7:$L$29,8,FALSE))

For reasons of sanity, I prefer the first.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions