Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

This is a discussion on Standard VLOOKUP but need to replace #N/A with blank within the Excel Questions forums, part of the Question Forums category; Hi, How do you change this VLOOKUP so that if the result is #N/A, and if the field is blank, ...

  1. #1
    New Member
    Join Date
    Sep 2004
    Posts
    30

    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,213

    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
    885

    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

    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
    885

    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,253

    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

    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,253

    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

    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
    65,898

    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.

Page 1 of 2 12 LastLast

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