Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Lookup formula error

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a form that I use that collects numerical data from several operations. I use lookups to bring that data into another worksheet. My problem is that not all values I search for are always present.
    Is there a way to have the lookup return a "0" instead of the "#N/A" error if the value I am looking up is not present?
    I thought that I had seen this posted here before, but I have been unable to find it.

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use:

    =IF(ISNA(VLOOKUP($C$10,Suppliers!$A$2:$B$28,2,FALSE)),"",VLOOKUP($C$10,Suppliers!$A$2:$G$28,4,FALSE))

    This is one that I use. If it gets an #N/A error it puts in "" (blank) otherwise it just puts the lookup in.

  3. #3
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    865
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is annoying, but the solution that I use is as follows:

    Let's say your lookup is:
    =VLOOKUP(A1,$Z$1:$AA$99,2,FALSE)

    Then I would use
    =IF(ISNA(VLOOKUP(A1,$Z$1:$AA$99,2,FALSE)),0,VLOOKUP(A1,$Z$1:$AA$99,2,FALSE))

    The ISNA() function tells you if you are going to get an N/A. If you are, then the IF statement puts a zero, otherwise, it lets the VLOOKUP recalc.

    Bill

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another one:

    =IF(countif($A$2:$A$28,C10)=0,"",VLOOKUP($C$10,Suppliers!$A$2:$G$28,4,FALSE))

    This post has come up time to time

    Adam

Some videos you may like

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
  •