Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Standard VLOOKUP but need to replace #N/A with blank

  1. #1
    New Member
    Join Date
    Sep 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Standard VLOOKUP but need to replace #N/A with blank

    Hi,

    How do you change this VLOOKUP so that if the result is #N/A, and if the field is blank, the result will be either "no" or a blank field?

    =VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    =IF(COUNTIF('PROCESS Trk'!$C$9:$C$53,A8)=0,"Not Found",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

    Denis

  3. #3
    Board Regular
    Join Date
    Dec 2003
    Location
    New-Delhi
    Posts
    891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    Hi,

    Try..

    =if(isna(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)),"",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

    Hope this what u have been looking for....

    Saurabh...

  4. #4
    New Member
    Join Date
    Sep 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    Thanks thats great, but the blank fields that the sheet is looking at come up as date 00/01/1900, whereas I need them to be "Not Found" as well.

    can you modify? or do I have to change my formatting?

  5. #5
    Board Regular
    Join Date
    Dec 2003
    Location
    New-Delhi
    Posts
    891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank


    Thanks thats great, but the blank fields that the sheet is looking at come up as date 00/01/1900, whereas I need them to be "Not Found" as well.

    can you modify? or do I have to change my formatting?
    _________________
    honda

    Try...

    =if(isna(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)),"Not Found",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

    Saurabh...

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,442
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    Quote Originally Posted by coffee11
    Hi,

    How do you change this VLOOKUP so that if the result is #N/A, and if the field is blank, the result will be either "no" or a blank field?

    =VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)
    There shouldn't be a blank field, if no match is found, it'll return #N/A

    See,

    http://www.mrexcel.com/board2/viewtopic.php?t=62102

  7. #7
    New Member
    Join Date
    Sep 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    some of the blank fields are returning dates of 00/01/1900 - do you why this is?

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,442
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard VLOOKUP but need to replace #N/A with blank

    Quote Originally Posted by coffee11
    some of the blank fields are returning dates of 00/01/1900 - do you why this is?
    Because the cell is formatted as a date or the lookup table is formatted as dates.

  9. #9
    New Member
    Join Date
    Sep 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Does anybody know how to VLOOKUP dates without returning....

    the empty fields in the VLOOKUP as 00/01/1900 (I want them to be empty)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    80,731
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Does anybody know how to VLOOKUP dates without returning

    Quote Originally Posted by coffee11
    the empty fields in the VLOOKUP as 00/01/1900 (I want them to be empty)
    If you installed the morefunc add-in...

    =IF(ISNA(SETV(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,0))),"",IF(GETV(),GETV(),""))

    If you can't use morefunc for some reason...

    =IF(ISNA(V(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,0))),"",IF(V(),V(),""))

    What follows is the code for the V() function and instruction how to add it to your workbook:

    Public Function V(Optional vrnt As Variant) As Variant
    '
    ' Stephen Dunn
    ' 2002-09-12
    '
    Static vrntV As Variant
    If Not IsMissing(vrnt) Then vrntV = vrnt
    V = vrntV
    End Function

    To add this to your workbook:

    Activate Tools|Macro|Visual Basic Editor;
    Activate Insert|Module;
    Copy the UDF above and paste it in the pane entitled "...(code)".
    Activate File|Close and Return to Microsoft Excel.
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com