ISNA retuning "0" for a blank cell, Want to keep it blank

Hi All,
I am a long time reader here but this is my second post. My Master-tracker is using two different worksheet as reference. I am using the following formula to get the values; however it returns "0" if its not getting any value in the source file.

=IF(ISNA(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6.MNISData'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0),VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0))

Can you please help to get rid of this "0". I am using excel-2007.

Thanks in advance!

Hi Safaet,

Try changing the last syntax of the formula to "" as shown in below formula:-

=IF(ISNA(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6.MNISData'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0),"")

Regards,
DILIPandey

<click on below 'star' if this helps>

Is it possible that the reference that you are looking for is not in either file? perhaps that is why the results are not as expected?

=IF(ISNA(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6.MNISData'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0),VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0))

just breaking down your formula
first formula
logical because of the if= IF(ISNA(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6.MNISData'!$A$2:$DG$1006,23,0))

second formula
value if true -----VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0)

third formula
value if false ---VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0))

where we'll the "" space be..

why not use iferror as you are using 2007?

iferror(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0))

then to get blank if result is 0
if(iferror(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0))=0,"",iferror(VLOOKUP(A5,'[1112_Infill_WBB_Address_Analysis.xlsx]6. MNIS Data'!$A$2:$DG$1006,23,0)),VLOOKUP(A5,'[1112_Infill_Metro_Growth_Analysis.xlsx]6. MNIS Data'!$A$2:$DH$486,23,0)))

but it would be easier to custom format the cell 0;-0;;@