Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Replacing the False in vLookup with a Dash

    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. :-)

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Keeping with the VLOOKUP() theme you are using, to return a null string "" in Excel 2003:
    Code:
    =IF(ISNUMBER(MATCH(K12,Events!$A$2:$A$2000,0)),
        VLOOKUP(K12,Events!$A$2:$D$2000,4,FALSE),"")
    If you want it to show a "-" when a match isn't found then
    Code:
    =IF(ISNUMBER(MATCH(K12,Events!$A$2:$A$2000,0)),
        VLOOKUP(K12,Events!$A$2:$D$2000,4,FALSE),"-")
    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...

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Excellent Colin! Thanks for the modified VLOOKUP formulas as well as the new function information in 2007.


  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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:
    Code:
    =MATCH(K12,Events!$A$2:$A$2000,0)
    Then, in the cell, say L12, where I want the proper lookup result to be returned, I use this:
    Code:
    =IF(ISNUMBER(N12),INDEX(Events!$D$2:$D$2000,N12),"")



    When you're using VLOOKUP(), there's a concern with the col_index number (highlighted in red):
    Code:
    =VLOOKUP(K12,Events!$A$2:$F$2000,4,FALSE)
    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:
    Code:
    =IF(ISNUMBER(MATCH(K12,Events!$A$2:$A$2000,0)),
        INDEX(Events!$D$2:$D$2000,MATCH(K12,Events!$A$2:$A$2000,0)),"")
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •