How to avoid: Vlookup returns zero if the target cell is blank

As much as I love VLOOKUP() is has one annoying habit of returning 0 if there is nothing in the target cell. This is particularly important if you need to validate between 0 and blank (for example you are building a forecast model that will take either the forecasted value or actual value - the actual value could be zero if there was no activity or blank if you have not yet reached that period).

The solution to this is complicated and so I will break it down in to its constituent parts.

1) In order to determine whether the target cell contains a numeric value we will use COUNTBLANK(). The problem with this is that COUNTBLANK() requires a cell reference or range (as opposed to COUNT() which just requires an array of values, which may or may not be cell references)

2) In order to get a cell reference we will use the CELL("address",[target cell]) syntax. This will return the reference in the format [sheet!][$ColID$Rownumber]. The problem is how to get the [target cell]. You can't use VLOOKUP() for this because it will only return the cell contents. instead we will use OFFSET(). You can't use the cell reference directly so we will wrap it in an INDIRECT().

3) OFFSET() will give us the cell n rows and m columns from the reference cell. We will know the reference cell because it will be the start of the VLOOKUP() range, but we don't know how far down the range it will be because the source value will be different for each search. To get round this we can use the MATCH() function.

4) MATCH() takes a source value and returns the number of cells in the range that had to be searched to find a match (position in the range array). If we limit the range to the 1st column in the VLOOKUP() then this will give us the row offset from our start point.

5) now to put it all together. Lets assume that the value we are looking for is in Sheet1!$B$1 and the value we want to return is in Sheet2, columnD. First we test to see if the cell is empty and if it is then we insert "", otherwise we insert the value of the cell (we can use VLOOKUP() for this as the syntax will be shorter)

=IF
(COUNTBLANK
(INDIRECT
(CELL
("address",OFFSET
(Sheet2!$B$1,MATCH(Sheet1!$B$1,Sheet2!$A:$A,0
)
,4
)
)
)=1
,""
,VLOOKUP
($B$1,sheet2!A:D,4,0),FALSE
)
)

In summary, this formula will check if the target cell is empty or not a number and if so return nothing ("") otherwise it will return the contents of the cell.