Hello,

Is there any way to replace the "#N/A" with a dash (-) or get a blank cell if there is no data matching the following vlookup?

=VLOOKUP(K12,Events!$A$2:$F$2000,4,FALSE)

Currently the "False" displays a "#N/A" in empty cells, but I would like to see a dash (-) or a blank cell instead.

I am using Microsoft Excel 2003.

Thank you. :-)

Hi,

Keeping with the VLOOKUP() theme you are using, to return a null string "" in Excel 2003:
If you want it to show a "-" when a match isn't found then
In Excel 2007 things are easier because there's a new function introduced called IFERROR() which can do this sort of thing more succinctly.

Hope that helps...

Excellent Colin! Thanks for the modified VLOOKUP formulas as well as the new function information in 2007.

You're welcome.

I actually do something slightly different in this situation which doesn't use VLOOKUP() at all.

I use a helper column which contains this formula copied down:
Then, in the cell, say L12, where I want the proper lookup result to be returned, I use this:



When you're using VLOOKUP(), there's a concern with the col_index number (highlighted in red):
The problem is it's a number - not a range reference - so, for example, if you were to insert or delete column B on that sheet, your VLOOKUP() formula would return the result from the wrong column (it wouldn't adjust).

Using the INDEX/MATCH combination works around this problem. The formula can also be more easily extended to become a double or conditional lookup. Of course you can do the INDEX/MATCH all in one formula if you want to; it would look like this:
The small points doing it in one formula are that:
[i] There's a small performance impact compared to the VLOOKUP() equivalent. I think on Charles Williams' site he estimates 5%. If you are looking up more than one value from the row then this impact increases. By using two separate formulas this impact is not a factor.
[ii] The formula is quite long so perhaps it is harder to read, digest and maintain than the two separate formula combination I tend to use.
[iii] Worksheets are large: don't be afraid to use a helper column.


If the data in column A in the lookup data is sorted, then there's an even better way of doing it.