Sheets: how to return NULL value in IF formula that looks up date value

6
votes

I'm pretty sure this is a simple formula. I'm in Google Sheets and I want to find the latest date for a value in one sheet based on the value found in another sheet, or return a null value (blank) if there i no match. Here's what I have now: 

=arrayformula(Max(IF(B285='2014 Donations Log'!F:F,'2014 Donations Log'!A:A)))

BUT. I want the cell to be BLANK if there is no corresponding value in 2014 Donations Log. If I add anything to the IF test, (such as ,"") then it returns a bogus date value that is misleading.

The column is formatted for date.

I need this as a formula.

Any ideas?

2
google-sheets
This question is not about programming as defined in the help center, perhaps it belongs on webapps.stackexchange.com/questions/tagged/google-spreadsheets. – Mogsdad
Mogasdad: Although its pushing the boundary a little, modern spreadsheets are turring complete pure functional "languages" of sorts (The VBA in excel is definately not functional, but the formula definition syntax in the cells is). – Shayne

2 Answers

3
votes

The solution to this is to refer to some known null column. You can even define that as a name range with a name NULL and then you can refer to NULL in your formulas. Alternative trick that may be applicable is that if your IF formula happens to be checking ISBLANK of some field and you e.g. want to only do something if some other field has a value.. well then you obviously have a guaranteed to be NULL field conveniently at your hands whenever it is "blank".

0
votes

To allow for the possibility that the maximum value is indeed 0 (rather than that there is no value): 

=ArrayFormula(if(iserror(match(B285,'2014 Donations Log'!F:F,0)),"",Max(IF(B285='2014 Donations Log'!F:F,'2014 Donations Log'!A:A))))