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

Показать описание

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
------------------------

Рекомендации по теме

Комментарии

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.

gonzinigonz

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.

zaklyles

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

lisajonescastronova

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

IvanCortinas_ES

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

limitone

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

vesaldiy

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

linjr.delaespriellag.

Thank you so much! You have saved me MONTHS of

CustomWeddingGarter

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

How to Return a Blank Cell If Zero in Excel? 3 Methods- IF Formula, Custom Formatting, Sheet Option

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

VLOOKUP: IF VALUE NOT FOUND Return BLANK or ZERO

How to Stop Zero when Copying Blank Cells in Excel

How to return BLANK instead of zero

How to replace 0 with blank cells Excel

#Shorts | Return a blank instead of a zero value using IF, LEN, and VLOOKUP

If Cell is Blank Then ... Return Value or Blank in Excel

Mastering PERL in VLSI: Marathon Episodes with Module Installation & Interview Q&A

Hide Zero Values in Excel | Make Cells Blank If the Value is 0

Excel: Return blank cell rather than zero if nothing is entered

How to Ignore Formula / Not Calculate If Cell is Blank in Excel

Excel: Make Vlookup to return blank instead of #N/A error

Excel - Vlookup and if condition - Replace 0 (zero) with blank

How to return 0 instead of BLANK in DAX

Replace #N/A Or Zero In VLOOKUP With Blank Or Specified Text In Excel

Blank Cells in Google Sheets? || If Cell is Blank Return Value or Blank

Power BI - Card visual - (Blank) to Zero - Implicit to Explicit measure

How to not calculate (ignore formula) if cell is blank in Excel

Show Blank Instead of 101900 in Excel | Don't Show 1900 on Blank Date | Leave Blank If No Date

Return blank or zero when referenced cell is blank in Excel

How to ignore formula (not calculate) if cell is blank in Excel

How To Fill Empty Cells With Zero Values in Excel | Replace Blank Cells With Zero in Excel

VLOOKUP if Blank then Return Blank?