# How to get array formula to return an empty cell instead of 00-Jan-00

1. ## How to get array formula to return an empty cell instead of 00-Jan-00

Hi Forum,

I have an array formula that takes a value in cell C2, finds any matching values in the range \$A\$2:\$A\$30000 and then returns the latest date associated with these values (the dates are in the range \$B\$2:\$B\$30000). This works well.

The problem I have is that when a date is not found in the range \$B\$2:\$B\$30000 it returns 00-Jan-00. I think this is because of the zero at the end of my formula which returns zero and then my date formatting shows that as 00-Jan-00. I need it to just show an empty cell ("") instead of 00-Jan-00.

``Please Login or Register  to view this content.``
I have tried using "" instead of zero (see formula below) but it returns #Value instead of an empty cell.

``Please Login or Register  to view this content.``
I also want it to return an empty cell ("") if the value in C2 is not found in the range \$A\$2:\$A\$30000. Again, it is presently returning 00-Jan-00 in these cases.

I have spent a lot of time experimenting with my formula but have not been able to get the result I need.

Any assistance would be greatly appreciated.

Dave

2. ## Re: How to get array formula to return an empty cell instead of 00-Jan-00

hello
can you try this one.

``Please Login or Register  to view this content.``
btw do you have a post like this recently i think i saw one like this few days ago..

Thanks.

3. ## Re: How to get array formula to return an empty cell instead of 00-Jan-00

Maybe:

=IF(MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0))=0,"",MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0)))

4. ## Re: How to get array formula to return an empty cell instead of 00-Jan-00

Thanks,

I will try these.

I did have a very similar post a few days ago. This problem is slighty different.

Thanks very much

Dave

5. ## Re: How to get array formula to return an empty cell instead of 00-Jan-00

@vlady: the formula isn't returning an error, it's returning zero which the format presents as 1 Jan 1900.

Regards, TMS

HTML Code:
``````Code		AOS Forecast	CODE Number 	Latest AOS 	TMS		Vlady
in P6		Forecast Date
1301-BL-001	04-Jul-10	1301-BL-001	04-Jul-10	04/07/2010	04/07/2010
1301-CR-001	18-Aug-10	1301-CR-001	18-Aug-10	18/08/2010	18/08/2010
1301-CR-001			1301-HE-001	00-Jan-00			00/01/1900
1301-CR-001	18-Aug-10	1301-HE-002	00-Jan-00			00/01/1900
1301-CR-001	18-Aug-10	1301-HE-003	00-Jan-00			00/01/1900
1301-CR-001	04-Jul-10	1301-PU-002	00-Jan-00			00/01/1900
1301-CR-001	04-Jul-10	1301-PU-003	00-Jan-00			00/01/1900
1301-CR-001	04-Jul-10	1301-PU-004	00-Jan-00			00/01/1900
1301-CR-001	04-Jul-10	1301-PU-005	00-Jan-00			00/01/1900
1301-CR-001	04-Jul-10	1301-PU-006	00-Jan-00			00/01/1900

E2:  	=IF(MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0))=0,"",MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0)))
F2:  	=IF(ISERROR(MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0))),"",MAX(INDEX(IF(\$A\$2:\$A\$30000=C2,\$B\$2:\$B\$30000),0)))``````

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1