(Vlookup)Returning 0 If cell is empty

Hi guys, so i am currently using this formula but i am baffled as to why it is returning 0 , if the cell its looking in is blank?

Please Login or Register  to view this content.

Thanks.

It will return 0 if column Q (16th column) is blank.

Is there a way to have it return blank?

No: if you want blank you will have to test for return of 0

=IF(C6="","",IF(VLOOKUP(C6,INDIRECT("'"&G4&"'!$B$3:$BA$318"),16,FALSE)=0),"",VLOOKUP(C6,INDIRECT("'"&G4&"'!$B$3:$BA$318"),16,FALSE)))

OR format cells to hide zeros

What type of value does the vlookup return when it's resulting cell is NOT empty, Text or Number?

If it's usually text, try
=IF(C6="","",T(VLOOKUP(C6,INDIRECT("'"&G4&"'!$B$3:$BA$318"),16,FALSE)))

If it's a number, try
=IF(C6="","",IFERROR(1/(1/VLOOKUP(C6,INDIRECT("'"&G4&"'!$B$3:$BA$318"),16,FALSE)),""))


If it could be either, then you're stuck with the brute force method Sambo posted, if vlookup = "" then "" otherwise vlookup

Originally Posted by JohnTopley

No: if you want blank you will have to test for return of 0

No, test for "". An actual zero in the cell may be a possibility, and is a real/actual value.

That worked, thanks sambo

Yours also worked, jonmo1