Return Blank Instead of Zero With VLOOKUP, XLOOKUP or INDEX MATCH | Return Blank Instead of 1/0/1900

Download the featured file here: www.bluepecantraining.com/wp-...
In this Microsoft Excel video tutorial I explain how to return a blank instead of a zero when performing a lookup. I also explain how to return a blank instead of the date 1/0/1900. This method works for VLOOKUP, XLOOKUP and INDEX MATCH.
Table of Contents:
00:00 - Introduction
Custom Number Format Solutions to Replace Zero with Blank
00:27 - Custom number format to replace 1/0/1900 date with blank
01:57 - Custom number format to replace zero with blank
02:44 - Custom number format to return blank instead of zero for text lookup
Formula Solutions to Replace Zero with Blank
03:42 - IF INDEX MATCH ="" (empty text string)
04:38 - IF ISBLANK INDEX MATCH
05:16 - IF LEN INDEX MATCH
06:14 - IF VLOOKUP
06:38 - IF XLOOKUP
------------------------

[@gonzinigonz]

I wish i found this video a month ago. There's so much to learn with excel, I'm just working it out as i go... I've been using it in a limited way for years.
I've been setting the sheets to not display zeros globally in program options where it was a problem for me but sometimes seeing a zero is a good thing.

[@lisajonescastronova5599]

OMG. THANK YOU times a million!!! This instructional video will save us so much time in the future!

[@CustomWeddingGarter]

Thank you so much! You have saved me MONTHS of work!!!!!!

[@mariomaluendarojas29]

beautifullll

[@zaklyles5943]

Thank you so much! I was dealing with a super weird issue where my master sheet was performing multiple vlookups on a 2nd worksheet. For some reason, one date column was returning zero, while the other was just returning a blank cell. I wanted a blank cell to return a blank value, but I was getting different results. It made no sense, but the custom format with ;;; was a super easy workaround.

[@IvanCortinas_ES]

Excellent tutorial Chester. Another great video. Thank you for these techniques!

[@ChesterTugwell]

Glad you liked it!

[@limitone9564]

I just sub'd.thank you!.. is this the same when linking 2 workbooks which already had a link formula on the cells?

[@linjr.delaespriellag.6776]

​
For Colombia use the following format DD/MM/YYYY;;
Thank you...

[@vesaldiy8569]

how even though it's blank, the countif / sumif formula doesn't calculate it?

[@ceyhunkvrak7791]

why no video today :)