Lookup formula error
Lookup formula error
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
    858
    Post Thanks / Like
    Mentioned
    4 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,305
    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

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