Vlookup to return blank instead of 0 or N/A in Excel

[Ansiep1]

Dear all,

I am new to this forum. My vlookup entry:

=VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0)

I would like to return completely blank cell, not 0. I tried several suggestions found on internet, but to no avail. Any assistance will be appreciated.

 

[oldbrewer]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

it is not clear to me what you are doing. You seem to be returning a value 34 columns to the right of your "match"........

 

[Ansiep1]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

Yes, I am summarising payment info from 2017 worksheet, but if the cell in sheet 2017 is blank, I need the return cell to be blank too. is there a better way to do this?

 

[Tetra201]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

Try this:

=IFERROR(1/(1/VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0)),"")

 

[Nishant94]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

Maybe custom format the column with something like this:

0.00;-0.00;;@

 

[Nishant94]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

If you want the formula to do it itself then,

=IF(VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0)=0,"",VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0))

This will work even if all your outcomes are not numbers.

 

[Ansiep1]

Re: How to vlookup to return blank instead of 0 or N/A in Excel

Try this:

=IFERROR(1/(1/VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0)),"")

Thanks a million! it worked.

 

[Guru008]

Try

=iferror(VLOOKUP(Table1[@ID],'2017'!$A$1:$AH$60,34,0),"")