Vlookup to return zero if zero is found, blank if no value or lookup value isn't found

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Vlookup to return zero if zero is found, blank if no value or lookup value isn't found

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup to return zero if zero is found, blank if no value or lookup value isn't found

     
    Hi all, I have reviewed several threads on vlookups, and can't seem to find a solution to my problem.

    Lookup sheet contains data as follows:
    0301010 10%
    0301007 0%
    0301015

    Sheet 2 where I am performing the lookup contains data as follows:
    0301005
    0301010
    0301007
    0301015

    I need to return as follows on Sheet 2:
    0301005
    0301010 10
    0301007 0
    0301015

    I need to return blanks where a value is not found in the vlookup or where a blank value is found, but return a zero if a zero value is found. I have tried using the ISNA, ISERROR, and various others solutions from other vlookup threads and cannot figure out how to get this to work. Any help is appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup to return zero if zero is found, blank if no value or lookup value isn't found

    Welcome to MrExcel.

    If the lookup table is in Sheet1 try:

    =IF(COUNTIF(Sheet1!A:A,A1),IF(ISNUMBER(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,FALSE))),VLOOKUP(A1,Sheet1!A:B,2,FALSE)*100,""),"")
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Jul 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup to return zero if zero is found, blank if no value or lookup value isn't found

      
    Thank you so much, Mr Poulsom! Worked wonderfully. I appreciate the quick response as well as the solution. You guys rock!

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