function of & in VLOOKUP formula?

**wgog** · 03-02-2011, 07:09 PM

I am using the following statement to avoid errors if there is a blank cell in a vlookup
=IFERROR(VLOOKUP($A19,Summary!$1:$1048576,15,FALSE)&"","").
This eliminates the N/A and 0 Jan 00 errors I was having before, which weren't consistantly trapped by a IFERROR(VLOOKUP...) statement, but the formula is returning a number rather than a date. Any idea how to fix it? I have tried reformatting the cell receiving the information. What is the function of the & in the formula?

**daddylonglegs** · 03-02-2011, 07:15 PM

The problem here is that by adding &"" you are converting the returned value to text, that's not good for a date because you just get the date serial number as a text value, best to revert to this

=IFERROR(VLOOKUP($A19,Summary!$1:$1048576,15,FALSE),0)

Now format result cell with this custom format

d mmm yy;;

Note the two semi-colons at the end, that will prevent you getting 0 Jan 00 (although the underlying cell value will still be zero when it looks blank)