Excel. Again.
There's no way to have a formula return a result which is NULL, basically the same as a blank cell.
If there's a formula, it must evaluate to something, and that something cannot be the equivalent of an empty cell.
So if a VLOOKUP has unmatched results, you get #N/A. That's fine, and to be expected in a big table.
But I don't want failed lookups to contain #N/A, because that breaks subsequent processing.
<Tappity-tap>
So I wrap the VLOOKUP in IFERROR, and supply a custom value for failed lookups.
I can't supply NULL as a value here.
I can supply Zero, but that gives the Wrong Answers, because Zero is an actual data value and will skew subsequent processing.
I can supply "", and that *looks* like it works.
But it doesn't, because it gives a cell that looks empty, but is not. It contains an empty string, which blows up subsequent processing.
I *really* don't want to have to re-engineer *everything* around this, and wrap all the post processing in protective code to trap the #N/As.
So I have had to resort to a stupid little VBA subroutine which checks over the VLOOKUP cell range and if it contains #N/A does a ActiveCell.clear on it.
What an effing hassle for the want of being able to specify a value something like NULL or Stupid.Excel.Blank!