Best way for VLOOKUP to deal with blank cells

[StarliteLemming]

When using Excel to clean tabular data, one issue that often pops up is that VLOOKUP returns zero '0' for empty cells.

What is the best way to deal with this?

Why the Obvious is Unsatisfactory
The obvious thing to do is:

=IF(VLOOKUP($A3, tblData, COLUMN(B3), FALSE) = "", "", VLOOKUP($A3, tblData, COLUMN(B3), FALSE))

The problem with this should be fairly obvious: the complexity of the formula is doubled. Here, I've used a flexible way to specify the column, but there are cases where $A3 might be replaced with a complex expression.

Of course, I may be worried about nothing, but it feels cumbersome and inefficient for my computer.

Note that using an array formula is generally not an option because it's difficult to extend the table or insert rows.

Another Possibility
It occurred to me to wrap the VLOOKUP() in a SUBSTITUTE() like so:

=SUBSTITUTE(VLOOKUP(...), "$0^", "")

but I don't think Excel understands that kind of regular expression in SUBSTITUTE().

Forcing an Error
Ideally, I want a zero value to force an error, as per:

=IFERROR(SOMETHING(VLOOKUP(...)), "")

but I can't think of anything that would work, where adding zero can work great in the inverse case.

Conclusion
Am I stuck with repeating the VLOOKUP() expression, or is there another way?

Thanks in advance!

PS: I suppose I could use VBA to create a UDF, but that seems like a lot of work, and is probably not portable to Google Sheets (for some projects).

 

[Alex Blakenburg]

Is your return value Text ?
If it is just add

 & ""

after your vlookup function

 

[StarliteLemming]

Is your return value Text ?
If it is just add

 & ""

after your vlookup function

No. My return value should be blank, but instead I get a zero.

 

[Fluff]

Is the vlookup returning text or numbers?

 

[StarliteLemming]

Is your return value Text ?
If it is just add & "" after your vlookup function

Turns out, your suggestion works perfectly! The following gets me the blank that I'm after:

=VLOOKUP($A3, tblData, COLUMN(B3), FALSE) & ""

Thank you!
(Sorry that I didn't understand what you were getting at.)

 

[StarliteLemming]

Is the vlookup returning text or numbers?

I feel like this question doesn't quite make sense, in the context. Which is why I didn't initially understand.
From my point of view, VLOOKUP() is returning whatever is in the source table. When the source cell is blank, it returns a zero instead of a blank, but Alex's suggestion prevents that beautifully. It's effectively the equivalent of forcing text to cause an error by adding zero.

 

[Fluff]

If the formula returns numbers then by adding the &"" will convert those numbers into text, which is why I asked if your formula returns text or numbers